MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Format For Telephone


Posted by Victor on December 04, 2001 1:20 PM

Surely this has been asked multiple times before but I can't find by searching the site. Have a long column with phone numbers formatted 555 1234567, want to change all to format 555 123-4567. Tried "Format, Cells, Special, Phone Number", but it doesn't seem to do anything.
Any ideas?

Posted by Mike on December 04, 2001 1:23 PM

Get rid of the space b4 format

Get rid of the space and then use format.

Posted by Mark W. on December 04, 2001 1:28 PM

You'll need to do more than eliminate the space...

...you'll also need to convert it to a numeric
data type. I recommend that you use the formula,
=SUBSTITUTE(A1," ","")+0, to convert a text value
in cell A1 and format it as 000 000-0000. Before
you delete the original values you'll want to
perform a Copy/Paste Special Values on the cells
containing this formula.

Posted by IML on December 04, 2001 1:30 PM

Re: Get rid of the space b4 format

Victor
In case you don't know, a quick way to do this is to highlight your phone number row, hit control F for find and hit your space bar once in the find section. the click replace and finally replace all.

Good luck

Posted by Mark W. on December 04, 2001 1:30 PM

You could also...

Use Find/Replace to substitute " " with "". Then
use the Data | Text to Columns... menu command to
re-parse the text value and cause it to be
coerced into a numeric one. And, then format the
column as 000 000-0000.

Posted by Bariloche on December 04, 2001 9:00 PM

The simpler solution(?)

Victor,

Assuming that you don't have a preceding or trailing blanks, just use:

=LEFT(A1,7)&"-"&RIGHT(A1,4)

If you do have blanks, then "Trim" the value first, i.e, Trim(A1)

Modify the cell reference as a necessary, of course.

enjoy

Posted by Barry Katcher on December 05, 2001 2:00 PM

Actually, for your format you should create a custom format (not the pre-defined Special format). The format is ### ###-####

I think, though, that you'll have to re-input all the numbers. Formatting the cells with the existing numbers will not change their format.