Adding Prefix

[Guest]

Dear all,

Suppose I want to input an extension number (e.g. 61151) in cell A1 then I
want a full telephone number appears in B1. Our telephone prefix is 4255 so
the full telephone number should be 4255 1151. My formula in B1 is:

=IF(LEFT(A1)=6,"4255"&RIGHT(A1,4),"")

Why I use LEFT(A1)=6 becuase I have another set of extension numbers start
with 4. However, the result of the above formula returns a blank cell. Where
is the mistake? Please kindly advise.

Thanks & regards.

 

[Guest]

If telephone numbers are text, try:

=IF(LEFT(A1)="6","4255"& RIGHT(A1,4),"")

 

[Leo Heuser]

Dear all,

Suppose I want to input an extension number (e.g. 61151) in cell A1 then I
want a full telephone number appears in B1. Our telephone prefix is 4255
so
the full telephone number should be 4255 1151. My formula in B1 is:

=IF(LEFT(A1)=6,"4255"&RIGHT(A1,4),"")

Why I use LEFT(A1)=6 becuase I have another set of extension numbers start
with 4. However, the result of the above formula returns a blank cell.
Where
is the mistake? Please kindly advise.

Thanks & regards.

The function LEFT() always returns a string, so you can use

=IF(VALUE(LEFT(A1))=6,"4255"&RIGHT(A1,4),"")
or
=IF(LEFT(A1)="6","4255"&RIGHT(A1,4),"")

 

[MartinW]

Hi Freshman,

Your LEFT formula is missing the number of digits.

So
=IF(LEFT(A1)=6,"4255"&RIGHT(A1,4),"")
Should be
=IF(LEFT(A1,1)=6,"4255"&RIGHT(A1,4),"")

HTH
Martin

 

[MartinW]

Ignore this post, I forgot to engage brain before typing. ;-)