Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
In 2006 Pete_UK responded to Disaster lady regarding changing a large column
of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
=IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8))
So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
Gary,
Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
=IF(LEN(A1)=8,"(406)"&A1,"(" & LEFT(A1,3)&") "&RIGHT(A1,8))
Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 16:52:02 -0800, Cody Kid wrote: Gary, Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
Hello again Gary,
Alas, it did not work. Obviously I did not have the steps down correctly. I copied the formula you gave me into a cell in a blank column (Q) next to the phone numbers (P). Then I copied it down say 50 lines as a test. I then did a cut and paste (per Pete's instructions) from column Q to Column P. It just gave me the same ( ) with a number that was in Q. Obviously I missed a step or didn't give you enough or correct informaiton. Also, Is there a way in your formula, as with Pete's, to add the area code to the phone numbers that do not have one? Thank you "Cody Kid" wrote: Gary, Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
Thank you Gord, I will try yours as I can see it is putting in the area code.
Maybe it will make a difference or maybe I don't know what i am doing yet? Thanks "Gord Dibben" wrote: =IF(LEN(A1)=8,"(406)"&A1,"(" & LEFT(A1,3)&") "&RIGHT(A1,8)) Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 16:52:02 -0800, Cody Kid wrote: Gary, Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
Here is the new formula:
=IF(LEN(A1)=8,"(406) " & A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) It assumes your input is in A1 -- Gary''s Student - gsnu200770 "Cody Kid" wrote: Gary, Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here we are with the phone numbers again. I need to convert
Ahhh, Gary it worked. I think I just failed to change the cell #'s. There
is one item I would like to add to that formula. We import worksheets from various places and everyone wants to do it different. So we end up no zip - (got that solved) - no parenthesis around the area code (got that solved) but if an area code already has the ( ) around the area code - the phone number comes up incomplete. If there is no fix to that, I can always sort and only do the phone numbers that need the correction, then sort it back the way we want it. But if there is a fix or whatever to add to that formula that would be great. Thank you so much - how do you learn how to write these formula's Cody "Gary''s Student" wrote: Here is the new formula: =IF(LEN(A1)=8,"(406) " & A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) It assumes your input is in A1 -- Gary''s Student - gsnu200770 "Cody Kid" wrote: Gary, Thank you so much. Now is there a formula that will put the area code 406 on all the phone numbers with no area code? This is great, I am going to go try it right now. Thanks, Cody "Gary''s Student" wrote: =IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8)) So 123-456-7890 will return (123) 456-7890 and for no area code 456-7890 will return 456-7890 (same thing) -- Gary''s Student - gsnu200770 "Cody Kid" wrote: In 2006 Pete_UK responded to Disaster lady regarding changing a large column of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone numbers did not have an area code. She wished her entire column to be formatted as follows: 1-xxx-xxx-xxxx. I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area code. Pete resolved Disaster Lady's problem with the following formula: =IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2) and copy down to AM2500. This assumes the phone numbers are in the two formats described, and works by examining the first character of the number - if it is "(", as in "(716) 555-1212", this will be converted to "1-716-555-1212", otherwise it will have "1-585-" appended to the beginning of it. If you want these converted numbers to replace the ones you have, then highlight the cells AM2:AM2500, click <copy, then Edit | Paste Special | Values (check) | OK then <Enter - this will have fixed the values in column AM. You could then <cut these values and <paste them to overwrite the values in column AE. Hope this helps. Pete I am not experienced enough to understand this formula to make the changes to convert my columns to the proper format. Can someone help? Thanks - CodyKid |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert phone to *** | Excel Worksheet Functions | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
How can I cross reference phone numbers with existing phone numbe. | Excel Discussion (Misc queries) | |||
Words > Numbers (i.e. Vanity Phone Numbers) function | Excel Worksheet Functions |