Formatting phone numbers

[LMR]

I am trying to format 2,000+ phone numbers from this format: 555-555-5555 h
to (555) 555-5555. Can anyone help with a formula to include the parenthesis
and remove the letter "h" at the end?

Thanks,
LMR

 

[Gemini]

LMR, which version of Excel are you using? Also, are these phone numbers in
one single column? Once the transformation is achieved, do you plan to keep
the old numbers or get rid of them?

-- Gemini

 

[LMR]

I have two columns of numbers. I'd like to get rid of the old formatted
numbers after the transformation.

Thanks for your fast response!!!!

 

[MartinW]

Hi LMR,

Provided all of your data is exactly as posted and assuming
it is in column A put this in B1 and drag down to the end
of your data.
="("&LEFT(A1,3)&") "&MID(A1,5,8)

Alternatively if there are no blanks in column A just highlight
B1 and double click the fill handle, instead of dragging down.

HTH
Martin

 

[Gemini]

Hi LMR! The solution posted by MartinW will work for you. Couple of points
you might want to consider.

1) If you need a space after the ")", then use ") " (there's a space
included) rather than the ")".
2) Assuming your original data is in Col. A and you've put the formula
proposed by MartinW in col. B, once you have the formatted data in col. B, do
this. Click on top of col. B (the "B" itself). That selects the entire
column. Then copy the entire column to the clipboard (Ctrl-C). Then select
another empty col, eg. col C. Click in cell C1 and then do a Paste Special,
selecting Values. That'll copy all the values in col B into col C, WITHOUT
copying the formula in col. B. After you've done this, you can choose to
delete col A (the one with the old format) and col B (since that's dependent
upon the existence of data in col A).

Feel free to ask if something isn't clear.

HTH

-- Gemini

 

[MartinW]

Hi Gemini,

Couple of points you may wish to consider.

1) > Hi LMR! The solution posted by MartinW will work for you.

Well that is still a bit unsure, the OP's second post is a bit vague as
to the way their data is setup. We'll have to wait until they get back
to us on that count.

2) > If you need a space after the ")", then use ") " (there's a space

included) rather than the ")".

The formula I posted has a space in it. When using formulae from the
newsgroup it is much better to copy and paste from the post
rather than retyping it. It stops you missing the little details.

3) > Click in cell C1 and then do a Paste Special,

selecting Values. That'll copy all the values in col B into col C,

No need to use column C. You can just Paste Special>Values
on column B straight over the top of itself.

Regards
Martin