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.
