Pau Lo
asked on
=IF(LEFT(A1,1)="0",A1,CONCATENATE("0",A1)) issue
I am using =IF(LEFT(A1,1)="0",A1,CONC
the column (A:A) is formatted general (have also formatted them text but the same issue applied), yet when I apply this formula, its returning a completely random number if the field in column A:A already start with a 0, and the forumula is only returning the correct values if the value in A:A doesnt start with 0. Any ideas why?
I was also hoping to add a 3rd clause, whereby if there is no data in column A:A i.e. a blank cell, rather than just add a 0 with nothing else in column B, to do nothing at all, i.e. leave the cell blank.
the column (A:A) is formatted general (have also formatted them text but the same issue applied), yet when I apply this formula, its returning a completely random number if the field in column A:A already start with a 0, and the forumula is only returning the correct values if the value in A:A doesnt start with 0. Any ideas why?
I was also hoping to add a 3rd clause, whereby if there is no data in column A:A i.e. a blank cell, rather than just add a 0 with nothing else in column B, to do nothing at all, i.e. leave the cell blank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also another way, if your mobile numbers are always 7777888888 10 digit and you want the leading zero on those which do not have leading zeros then. you can achieve via another method.
select that column of data and then right click and select format cells then go to custom and then put eleven zeros like shown in the attached screenshot.
Capture.PNG
select that column of data and then right click and select format cells then go to custom and then put eleven zeros like shown in the attached screenshot.
Capture.PNG
Also, this formula is bullet proof
it must work in any condition =IF(TRIM(A2)="","",IF(OR(L
it must work in any condition =IF(TRIM(A2)="","",IF(OR(L
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If none of the above are working please upload a simple workbook with samples of the values in column A, some which do work and some which don't.
Thanks
Rob
Thanks
Rob
An additional solution to add leading "0" only when numbers.
=TEXT(A2,REPT("0",LEN(A2)+
Then the number 123 and the text 0123 will both be 0123.
=TEXT(A2,REPT("0",LEN(A2)+
Then the number 123 and the text 0123 will both be 0123.
if it doesnt work in your file. i am not sure if you are putting the formula correctly.
make sure you original column do not have space. if it does then amended formula =IF(TRIM(A1)="","",IF(LEFT
Book1.xlsb