entering telephone numbers

**bella18** · 04-19-2010, 10:51 AM

Hi,

I am inputting a long list of phone numbers and I want to delete the gaps inbetwen them in each cell- for example 796 789 789 to 796789789. I have seen that you can use the substitute or find and replace functions- have tried both- pretty sure I am not entering it correctly! Also if I can then delete the spaces can I then prefix the numbers with a country code rather than having to inout each one by one.

Thanks!

Sorry perhaps this was unclear- the list of telephone numbers I am copying in already has the spaces

**NBVC** · 04-19-2010, 10:55 AM

If you want to get rid of spaces, why are entering them in the first place?

Why not just enter as 796789789?

Not sure I follow that part.

**bella18** · 04-19-2010, 11:06 AM

Hi,

I am copying a long list of phone numbers and I want to delete the gaps inbetwen them in each cell- for example 796 789 789 to 796789789. I have seen that you can use the substitute or find and replace functions- have tried both- pretty sure I am not entering it correctly! Also if I can then delete the spaces can I then prefix the numbers with a country code rather than having to inout each one by one.

Thanks!

**NBVC** · 04-19-2010, 11:09 AM

Say you have one of the numbers in A1, put this formula anywhere and reply with the result.

=CODE(MID(A1,4,1))

**SpeedingLunatic** · 04-19-2010, 11:11 AM

Please Login or Register  to view this content.

To add a country code of 001 (I also put a space after it, not sure exactly how you want it formatted).

Please Login or Register  to view this content.

**bella18** · 04-19-2010, 11:29 AM

Originally Posted by NBVC

Say you have one of the numbers in A1, put this formula anywhere and reply with the result.

=CODE(MID(A1,4,1))

the result is 32

**bella18** · 04-19-2010, 11:33 AM

Originally Posted by SpeedingLunatic

Please Login or Register  to view this content.

To add a country code of 001 (I also put a space after it, not sure exactly how you want it formatted).

Please Login or Register  to view this content.

Ok thanks that worked for cell A1- how to I get it to work for the whole of a column- as I said not great with Excel-
thank you

**NBVC** · 04-19-2010, 11:34 AM

If the country code is the same for all, then

="01"&Substitute(A1,Char(32),"") or ="01"&Substitute(A1," ","") copied down... where 01 is the country code A1 contains first entry.

If the country codes are not the same we will need to know how you would determine what country code applies.

To copy down, enter the formula in first cell, then click, hold and drag down the little black square at bottom right corner of cell....