Phone Number Formats

[Mikey54412]

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

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


"Mikey54412" wrote:

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

[bj]

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

"Mikey54412" wrote:

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

[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 ###-###-####.

"Mikey54412" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to convert an existing phone number format [+1 (###)
> ###-###]
> to this type of format ###-###-#### without rekeying the numbewrs

[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 Neville wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to convert an existing phone number format [+1 (###)
> > ###-###]
> > to this type of format ###-###-#### without rekeying the numbewrs

--

Dave Peterson