Phone Number Formats

M

Mikey54412

Is there a way to convert an existing phone number format [+1 (###) ###-###]
to this type of format ###-###-#### without rekeying the numbewrs
 
B

bj

I assume you meant [+1 (###) ###-####]
If this is a text format and you want it an a numerical format
try
=value(mid(A1,6,3)&mid(A1,10,3)&mid(A1,15,4))
and set the custom format as 000-000-0000
You may have to play with the start points in the Mid () I can't tell
whether there are some spaces there
If you want a text output try
=mid(A1,6,3) & "-" & mid(A1,10,3) & "-" & mid(A1,15,4))
with the same comment on starting place.
 
B

bj

I meant to mention you might want to check whether it is aready a number with
custom formating to create the output.
 
R

Richard Neville

Select all the phone numbers. Using the Replace function, "Find" all the
[ ], +, ( ), 1s, and space characters, and replace them with nothing,
leaving you with ### ###-####. As you replace, you will have to find and
replace these characters one at a time except for [+1 (, which occur in
sequence.

Then, you need another hyphen between ### and ###. Go to Format-Cells,
Number tab, and select Custom (last entry on list). In the dialog box to the
right type ###-###-####.
 
D

Dave Peterson

Be careful with the 1s.

Maybe getting all the characters at the beginning would be quicker:

Select your range
edit|replace
what: [+1 ( <-- open square bracket, plus, one, spacebar, open paren
with: (leave blank)
replace all



Richard said:
Select all the phone numbers. Using the Replace function, "Find" all the
[ ], +, ( ), 1s, and space characters, and replace them with nothing,
leaving you with ### ###-####. As you replace, you will have to find and
replace these characters one at a time except for [+1 (, which occur in
sequence.

Then, you need another hyphen between ### and ###. Go to Format-Cells,
Number tab, and select Custom (last entry on list). In the dialog box to the
right type ###-###-####.

Mikey54412 said:
Is there a way to convert an existing phone number format [+1 (###)
###-###]
to this type of format ###-###-#### without rekeying the numbewrs
Click to expand...
Click to expand...
 
Top