Excel formatting

[NoCalAdmin]

I have been giving an Excel file (version 2010) that contains a column with phone numbers that vary in their syntex.

Some are (123)-456-7890
others are 123-456-7890

I need all entries to reflect the +11234567890 no spaces and no characters

I can't seem to get a Custom format to stick....

I have over 100 entries to resolve

Can this be accomplished via formatting? Anyone got a cool script?

Thanks in advance

Tom

 

[SkipVought]

Hi,

Select the entire column.

Change the Number Format to GENERAL

If you still have parentheses or dashes, then do a Find/Replace for any such character.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

If your phone number text is in cell A2, place

="+1" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")","")

in cell B2.

Then (perhaps) copy the contents of cell B2 and paste-value back into A2, followed by deleting B2.