| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I format phone numbers in excel?
I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
If those () and -'s are really part of the cell (not formatting choices),
Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
If 'phone number in column A, then put this in B and copy down:
=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","") HTH "CBickley" wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
Format Cells Choose "Custom" Category In the "Type:" field on the
right type in "000-000-0000" |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
EditReplace
what: ( with: nothing replace all Same for ) Gord Dibben MS Excel MVP On Wed, 23 Aug 2006 10:44:01 -0700, CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
Thanks, this worked. Now I have a space in the middle of the numbers that I
need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
Add one more edit|replace
Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
I'm not sure why OP needs any more than two editreplaces to get rid of the ()
Some data looks like (xxx)xxx-xxxx Wants it to look like xxx-xxx-xxxx EditReplace what: ( with: nothing EditReplace what: ) with: - My first post was replace with nothing which was incorrect. returns xxx-xxx-xxxx I guess I don't understand why the need to trun the phone number into a number just to turn around and custom format as xxx-xxx-xxxx Gord On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson wrote: Add one more edit|replace Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson Gord Dibben MS Excel MVP |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
I think that there was an unfortunate line break in the middle of his examples:
I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I think the first example was really: (xxx) xxx-xxxx So there would have been an extra space in that string. And as a personal choice, I'd want all my data the same format--either all text or all numbers (nicely formatted). I'd convert any text strings xxx-xxx-xxxx to its number and then use format|cells to give the whole range a nice consistent look. Gord Dibben wrote: I'm not sure why OP needs any more than two editreplaces to get rid of the () Some data looks like (xxx)xxx-xxxx Wants it to look like xxx-xxx-xxxx EditReplace what: ( with: nothing EditReplace what: ) with: - My first post was replace with nothing which was incorrect. returns xxx-xxx-xxxx I guess I don't understand why the need to trun the phone number into a number just to turn around and custom format as xxx-xxx-xxxx Gord On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson wrote: Add one more edit|replace Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson Gord Dibben MS Excel MVP -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
And by having the format of the field consistent, I can use =vlookup() and
=match() without having to worry about if the data is text or numeric. Dave Peterson wrote: I think that there was an unfortunate line break in the middle of his examples: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I think the first example was really: (xxx) xxx-xxxx So there would have been an extra space in that string. And as a personal choice, I'd want all my data the same format--either all text or all numbers (nicely formatted). I'd convert any text strings xxx-xxx-xxxx to its number and then use format|cells to give the whole range a nice consistent look. Gord Dibben wrote: I'm not sure why OP needs any more than two editreplaces to get rid of the () Some data looks like (xxx)xxx-xxxx Wants it to look like xxx-xxx-xxxx EditReplace what: ( with: nothing EditReplace what: ) with: - My first post was replace with nothing which was incorrect. returns xxx-xxx-xxxx I guess I don't understand why the need to trun the phone number into a number just to turn around and custom format as xxx-xxx-xxxx Gord On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson wrote: Add one more edit|replace Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson Gord Dibben MS Excel MVP -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
Not seeing the line break, but sure enough could be there.
Now I'm all warm and fuzzy again and can quit stressing myself over sh.. like this. On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson wrote: I think that there was an unfortunate line break in the middle of his examples: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I think the first example was really: (xxx) xxx-xxxx So there would have been an extra space in that string. And as a personal choice, I'd want all my data the same format--either all text or all numbers (nicely formatted). I'd convert any text strings xxx-xxx-xxxx to its number and then use format|cells to give the whole range a nice consistent look. Gord Dibben wrote: I'm not sure why OP needs any more than two editreplaces to get rid of the () Some data looks like (xxx)xxx-xxxx Wants it to look like xxx-xxx-xxxx EditReplace what: ( with: nothing EditReplace what: ) with: - My first post was replace with nothing which was incorrect. returns xxx-xxx-xxxx I guess I don't understand why the need to trun the phone number into a number just to turn around and custom format as xxx-xxx-xxxx Gord On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson wrote: Add one more edit|replace Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format phone numbers in excel?
When life gets too tense, reduce it to one fifth!
(Sounds better than reading) Gord Dibben wrote: Not seeing the line break, but sure enough could be there. Now I'm all warm and fuzzy again and can quit stressing myself over sh.. like this. On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson wrote: I think that there was an unfortunate line break in the middle of his examples: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I think the first example was really: (xxx) xxx-xxxx So there would have been an extra space in that string. And as a personal choice, I'd want all my data the same format--either all text or all numbers (nicely formatted). I'd convert any text strings xxx-xxx-xxxx to its number and then use format|cells to give the whole range a nice consistent look. Gord Dibben wrote: I'm not sure why OP needs any more than two editreplaces to get rid of the () Some data looks like (xxx)xxx-xxxx Wants it to look like xxx-xxx-xxxx EditReplace what: ( with: nothing EditReplace what: ) with: - My first post was replace with nothing which was incorrect. returns xxx-xxx-xxxx I guess I don't understand why the need to trun the phone number into a number just to turn around and custom format as xxx-xxx-xxxx Gord On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson wrote: Add one more edit|replace Edit|Replace what: (spacebar) with: (leave blank) replace all CBickley wrote: Thanks, this worked. Now I have a space in the middle of the numbers that I need to delete (xxx xxxxxxx). How do I do this? "Dave Peterson" wrote: If those () and -'s are really part of the cell (not formatting choices), Then select the column and do 3 edit|replaces edit|replace what: ( with: (leave blank) replace all edit|replace what: ) with: (leave blank) replace all edit|replace what: - with: (leave blank) replace all Now all your entries should be real numbers and your format|cells|number tab choices should work ok. CBickley wrote: I have an excel file with a column for phone numbers. Some are (xxx) xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow the xxx-xxx-xxxx format but am unable to do so with the format cells. What do I need to do? -- Dave Peterson Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
| Is there a way to make Excel 2000 dial phone numbers? | Excel Worksheet Functions | |||
| Format sequence for phone numbers? | Excel Discussion (Misc queries) | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
| How do I add an area code to a list of phone numbers in Excel? | Excel Worksheet Functions | |||
Linear Mode
