Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat column of telephone numbers
I have a variety of formats in the one column of phone numbers. Some have
parentheses, some do not. I would like them to be all formatted the same way. It doesn't work through format the cell, special. The other resolution given assumes all formats are the same. What do I do with the parentheses on some of the entries? -- Maree |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat column of telephone numbers
you will need to delete the extra charecters so you can format them all the
same. Select Edit-Find Click Replace In the Find box insert an open parenthesis ( Leave the replace box empty Click Replace all Repeat the procedure for the following charecters ) (close parenthesis) - (dash) (space bar) you should now have a list of numbers, which can be formated anyway you want. For example you can use a custom format of (###) ###-#### to display 1234567890 as (123) 456-7890 You will need to do this because if the number is entered with parenthesis to begin with, it is treated as text instead of a number. And any custom number format won't be applide. "Maree" wrote: I have a variety of formats in the one column of phone numbers. Some have parentheses, some do not. I would like them to be all formatted the same way. It doesn't work through format the cell, special. The other resolution given assumes all formats are the same. What do I do with the parentheses on some of the entries? -- Maree |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat column of telephone numbers
I'd create a helper column that converted all the inputs to numeric values,
than apply the special formatting. If your phone numbers are in column A, starting in row 2, then enter in B2: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (A2,"(",""),")",""),"-","")," ","")). Autofill that formula through column B. Then apply your format and copy / paste special values to lock in the values in column B. HTH. --Bruce "Maree" wrote: I have a variety of formats in the one column of phone numbers. Some have parentheses, some do not. I would like them to be all formatted the same way. It doesn't work through format the cell, special. The other resolution given assumes all formats are the same. What do I do with the parentheses on some of the entries? -- Maree |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Consecutive Numbers down a column not to Exceed 49 | Excel Worksheet Functions | |||
column filtering to match identical numbers | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Add selected numbers in a column that are a different color | Excel Discussion (Misc queries) |