Phone # w/Letter formatting

[jillrussell]

I have a spreadsheet of 138 phone #'s (including area codes).

About 10 of the #'s have a letter in them (i.e. 734-R42-7422) (and no, I can't just change the R to "7" - that would be too easy)

Obviously, since they have both text and numerical characters, they won't format properly as phone numbers.

Does anyone know how can I format them to show as phone #'s ?

 

[bk]

I'm not totally following, you want the ones w/ text to show up as....what?


You want:

734-R42-7422

to result as:
734-R42-7422
or
734-742-7422

 

[jillrussell]

I need to keep the letter in there so the result needs to be
(734) R42-7422

 

[MrKowz]

In the cell next to it, put the following, assuming your first entry is in A1:

="(" & left(A1,3) & ") " & right(A1,8)

 

[jillrussell]

It works great, except I need the dash after the first three digits of the phone #.

It gives me

(734) 4211234

I need
(734) 421-1234

Many thanks

 

[MrKowz]

="(" & Left(A1,3) & ") " & mid(A1,5,3) & "-" & right(A1,4)

 

[jillrussell]

You're awsome - works great!

Thanks