Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Phone number formatting
I am having some difficulty formatting the cells for phone numbers. Here's
the issue: I have a predictive dialer and the information I am using came with a 1 at the beginning of some of the numbers that isn't compatable with the system. I've tried the formatting suggestions in the help section of excel, but it really isn't giving me a way to format all the cells in that column to remove the 1 and just have the 10 digit number. Any help would be greatly appreciated, or just an absolute that it can't be done so I can quit trying... Thanks so much!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Phone number formatting
Let's start with the basics: give us some examples of the phone numbers as
they're currently displayed (those with the 1 and those without) and also give examples of how you want them to appear. I've seen phone numbers dealt with time and time again in these forums and there's almost zero doubt in my mind that there is a solution to your problem. "ms_kitty007" wrote: I am having some difficulty formatting the cells for phone numbers. Here's the issue: I have a predictive dialer and the information I am using came with a 1 at the beginning of some of the numbers that isn't compatable with the system. I've tried the formatting suggestions in the help section of excel, but it really isn't giving me a way to format all the cells in that column to remove the 1 and just have the 10 digit number. Any help would be greatly appreciated, or just an absolute that it can't be done so I can quit trying... Thanks so much!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Phone number formatting
This is how the numbers appear: 12345678901 (1+area code+number)
I would like to take out the number "1" entirely and have it be just the area code and the number. Spaces are irrelevant, they can be there or not. Thank you so much for the help, any tips you can give are greatly appreciated! "JLatham" wrote: Let's start with the basics: give us some examples of the phone numbers as they're currently displayed (those with the 1 and those without) and also give examples of how you want them to appear. I've seen phone numbers dealt with time and time again in these forums and there's almost zero doubt in my mind that there is a solution to your problem. "ms_kitty007" wrote: I am having some difficulty formatting the cells for phone numbers. Here's the issue: I have a predictive dialer and the information I am using came with a 1 at the beginning of some of the numbers that isn't compatable with the system. I've tried the formatting suggestions in the help section of excel, but it really isn't giving me a way to format all the cells in that column to remove the 1 and just have the 10 digit number. Any help would be greatly appreciated, or just an absolute that it can't be done so I can quit trying... Thanks so much!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Phone number formatting
One approach to this would be to use TTC (Text To Columns).
TTC returns values without the need to remove intermediate formulas. Select the column of data and then: <Data <Text To Columns Click on "Fixed Width", then <Next, Click in the Preview Window, and position the break line to separate the first number 1 from the rest of the number, then <Next In the Preview Window, that first column containing the 1 is selected by default. Click on the "Do Not Import Column" option. Now, you have a choice as to whether or not you wish to *change the original* data, by just eliminating the first 1, OR ... you can retain the original data column, and create a new column of data without that first digit. To *change* the original data, simply click on <Finish To create a new column, retaining the original data, change the location in the "Destination" window from the default location of the original data, to any vacant column you wish to populate with this revised data, THEN click <Finish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ms_kitty007" wrote in message ... This is how the numbers appear: 12345678901 (1+area code+number) I would like to take out the number "1" entirely and have it be just the area code and the number. Spaces are irrelevant, they can be there or not. Thank you so much for the help, any tips you can give are greatly appreciated! "JLatham" wrote: Let's start with the basics: give us some examples of the phone numbers as they're currently displayed (those with the 1 and those without) and also give examples of how you want them to appear. I've seen phone numbers dealt with time and time again in these forums and there's almost zero doubt in my mind that there is a solution to your problem. "ms_kitty007" wrote: I am having some difficulty formatting the cells for phone numbers. Here's the issue: I have a predictive dialer and the information I am using came with a 1 at the beginning of some of the numbers that isn't compatable with the system. I've tried the formatting suggestions in the help section of excel, but it really isn't giving me a way to format all the cells in that column to remove the 1 and just have the 10 digit number. Any help would be greatly appreciated, or just an absolute that it can't be done so I can quit trying... Thanks so much!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Phone number formatting
Hi
Work on a copy of your data - just in case of mistakes!!! With data in column A enter in B1 =IF(LEFT(A1)="1",MID(A1,2,LEN(A1)-1),A1)) copy down column B as far as required To "fix" the data, copy the whole of column B then Paste SpecialValues to turn the formulae back into numbers You can then delete column A -- Regards Roger Govier "ms_kitty007" wrote in message ... This is how the numbers appear: 12345678901 (1+area code+number) I would like to take out the number "1" entirely and have it be just the area code and the number. Spaces are irrelevant, they can be there or not. Thank you so much for the help, any tips you can give are greatly appreciated! "JLatham" wrote: Let's start with the basics: give us some examples of the phone numbers as they're currently displayed (those with the 1 and those without) and also give examples of how you want them to appear. I've seen phone numbers dealt with time and time again in these forums and there's almost zero doubt in my mind that there is a solution to your problem. "ms_kitty007" wrote: I am having some difficulty formatting the cells for phone numbers. Here's the issue: I have a predictive dialer and the information I am using came with a 1 at the beginning of some of the numbers that isn't compatable with the system. I've tried the formatting suggestions in the help section of excel, but it really isn't giving me a way to format all the cells in that column to remove the 1 and just have the 10 digit number. Any help would be greatly appreciated, or just an absolute that it can't be done so I can quit trying... Thanks so much!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing data | Excel Discussion (Misc queries) | |||
Phone Number formatting | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Phone Dialer | Excel Discussion (Misc queries) | |||
Pivot Table Drill Down Number Formatting | Excel Discussion (Misc queries) |