Formating the Phone number with 4 to 04
=IF(LEFT(B3,1)="4", "04" & RIGHT(B3,LEN(B3)-1), IF(LEFT(B3,2)="04", B3, SUBSTITUTE(B3, "4", "04", 1)))
Let me break down how this formula works:
LEFT(A1,1)="4"checks if the first character of the cell A1 is "4".- If it is, then
"04" & RIGHT(A1,LEN(A1)-1)is executed, which means "04" is concatenated with the rest of the string starting from the second character. - If the first character is not "4", then it checks if the first two characters are "04" with
LEFT(A1,2)="04". - If the first two characters are "04", it leaves the number unchanged, otherwise, it uses
SUBSTITUTEto replace the first occurrence of "4" with "04".
Drag this formula down if you want to apply it to multiple cells. Adjust the cell references as necessary based on your data layout.
To remove spaces from a cell content in Excel, you can use the SUBSTITUTE function to replace all spaces with an empty string. Here's the formula:
=SUBSTITUTE(B3, " ", "")
This formula replaces all spaces in cell B3 with nothing, effectively removing them from the content.
Watch Video for More Details
