Phone Number

[aedislee]

Good afternoon

I have a list of client with telephone number. my problem is that some on the number have spaces in them

I want all the numbers to start with 44, and then the number

any help greatly appreciated

thanks

[Glenn Kennedy]

Assuming your numbers start in A1, use this:

=CONCATENATE(44,SUBSTITUTE(A1," ",""))

OK???

[aedislee]

thanks for the response

if the number is 01455851139 then it works, but if the number is 07598 560334, then it doesnt work

[Glenn Kennedy]

If you DO need to drop the first zero, then use this ine instead:

=CONCATENATE(44,SUBSTITUTE(IF(LEFT(A1,1)="0",SUBSTITUTE(A1,"0","",1),A1)," ",""))

[Glenn Kennedy]

Maks sure that you're copying my formula correctly. It seems to be working here OK. Click the select code button just above the formula to make sure you're getting it right.

BtW, you may wish to modify your request to get it to drop leading zeros from the numbers.

[AlKey]

Maybe this

=SUBSTITUTE("44"&A1," ","")

	A	B
1	01455851139	4401455851139
2	07598 560334	4407598560334

[Glenn Kennedy]

Was this solved? If so can you mark the thread as solved and (preferably) say thanks to those who helped by clicking the Add Reputation button at the foot of their post(s).