Formatting problem with telephone numbers in excel.

[Dale Lawrence]

I have a large excel file (905 names) where the telephone number is formatted xxx-xxx-xxxx.    The phonebank program I'm using will not accept numbers in this format - they need to be xxxxxxxxxx or (xxx)xxx-xxxx.

Can anyone tell me a quick way to change these numbers to either of the accepted formats.   The only way I know of is to retype them directly.

Thank you. 

[#2]

Hi Dale,

Just to clarify, you have Excel numbers like 1234567890 which are formatted as 123-456-7890; or you have text values like '123-456-7890 which you'd like to transform to another text?

[#3]

Hi @Dale Lawrence

Hopefully I understand you right.

Not the most elegant solution (@Sergei Baklan will probably have something much nicer) but I've created the following formula to hopefully achieve the format that you want:

=LEFT(A2,3)&LEN(A2)-8&LEN(A2)-7&LEN(A2)-6&RIGHT(A2,4) 

Place the code in the same row as the first cell and phone number, autofill downwards and that should convert all your numbers to this format. This assumes that your phone numbers are as you say in xxx-xxx-xxxx format.

Here's a sample spreadsheet for you to play with in regard to this formula.

Hope that helps with your desired solution!

Cheers

Damien

[#4]

Hi @Damien Rosario,

What I meant

- if texts simply select all cells, Ctrl+H and replace dashes on nothing

- if numbers with format like 999-999-9999 when change it on General

[#5]

Thanks for the info, Sergei. This is why you are my favourite Excel MVP!

[#6]

Thank you!