I’m trying to create a formula in excel using IF condition so that it adds 6 to the beginning of an existing number starting with 0, as in 0124685515. However, when the number starts with 1, the formula should add 60 to the beginning of the existing number. Otherwise, the number should be left untouched.
To be more specific:
124685515 will be 60124685515
0124685515 will turn into 60124685515
60124685515 should be 60124685515
My current attempt:
=IF(LEFT(A1,1)=0,"6"&A1,IF(LEFT(A1,1)=1,"60"&A1,A1))
How can I modify the formula to meet the above conditions?
>Solution :
It looks like the ‘numbers’ are actually strings (i.e. text). If that’s true:
a) If you want a textual result, alter your formula per:
=IF(LEFT(A1,1)="0","6"&A1,IF(LEFT(A1,1)="1","60"&A1,A1))
b) Alternately:
=IF(LEN(A3)<11,TEXT(A3+60000000000,0),A3)
c) If you want a numeric result: see @P.b’s response