Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home



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?

Get rid of the space b4 format

Posted 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 format

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

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


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
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


Re: Format For Telephone

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.




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.