Formatting phone numbers
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 |
Formatting phone numbers
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" wrote: 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 |
Formatting phone numbers
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!!!! "Gemini" wrote: 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" wrote: 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 |
Formatting phone numbers
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 "LMR" wrote in message ... 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 |
Formatting phone numbers
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 "LMR" wrote: 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!!!! "Gemini" wrote: 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" wrote: 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 |
Formatting phone numbers
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 SpecialValues on column B straight over the top of itself. Regards Martin "Gemini" wrote in message ... 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 "LMR" wrote: 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!!!! "Gemini" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 12:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com