Archive of Mr Excel Message Board
Back to Forms in Excel VBA archive index
Back to archive home
Format For TelephonePosted 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.
Get rid of the space b4 formatPosted by Mike on December 04, 2001 1:23 PM
Get rid of the space and then use format.
You'll need to do more than eliminate the space...Posted by Mark W. on December 04, 2001 1:28 PM
...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.
Re: Get rid of the space b4 formatPosted by IML on December 04, 2001 1:30 PM
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.
You could also...Posted by Mark W. on December 04, 2001 1:30 PM
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.
The simpler solution(?)Posted by Bariloche on December 04, 2001 9:00 PM
Assuming that you don't have a preceding or trailing blanks, just use:
If you do have blanks, then "Trim" the value first, i.e, Trim(A1)
Modify the cell reference as a necessary, of course.
Re: Format For TelephonePosted 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.
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.