Can't create a formula in excel to satisfy certain conditions-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

=IF(LEN(A1)=11,A1 0,A1 60000000000)

Or if you want to stick to text: =TEXT(IF(LEN(A1)=11,A1 0,A1 60000000000),"@")