Excel 2007 text to column problem.?

Question

I have 2 sets of data containing phone numbers.
In order for me to compare the data, I normally seperate the data into multiple columns using the text to columns function.
The second and third data part of my phone number revert to a zero value and therefore I do not have the ability to isolate the exact phone #'s from each data source.
Example (Part A): I want my phone # '(612) 497- 0010' , to show as fixed width | ( | 612 | ) | | 497 | - | 0 | 090 |
Example (Part B): Objective, after un-needed columned data is removed, (to display as in columns) | 612 | 497 | 0 | 010 | (to match to my second data set)
HERE'S MY PROBLEM: No matter what 'number formatting' that I apply to source data or the new data cells, I get the following unusable result: | ( | 612 | ) | | 497 | - | 0 | 10 | ( or if the # is '(623) 497- 9000' , I get: | ( | 623 | ) | | 497 | - | 9 | 0 | )
I need to get all the #'s in order for me to continue my phone number audit project.

Answer 1

I also tried doing the Text to Column on [phone].



In step 3 of the wizard (in Excel 2003), I selected the column at the end that has the 010, and I set the Column Data Format to Text.



This gave me the result "010" for that column.



Is this not happening for you? If that's not working for some reason, then you would need a formula as expletive_xom suggested in order to convert it to text and retain all 3 characters.



Feel free to e-mail a sample to [e-mail] and I'd be happy to take a look.



Best of luck.

.

Answer 2

what makes it unusable?

since you want 'number formatting' then 0 is the same as 000.

choose custom formatting.

put 000 in the "type" box.



if you want text formatting with 3 numbers then thats different.



so your last column shows 0 intsead of 000

the last column for me ended in column G

in H1 use this formula



=TEXT(G1,"000")



that will show the last 3 digits with the 0's, but of course its text not #

so if you need to use H1, you will have to use =value(h1) to turn it back into a number



does that help any?



edit-

so after you split the taxt to columns...are you then putting the numbers back together at some point?



just an idea

oh well, good luck to you .

Answer 3

Here is one way to do as you wish. Highlight cells C3:C134 Access Conditional formatting and enter this as the New Rule: =AND(C3="Avail.",H3=MAX(H:H)) Format the Fill color or Font as you wish and click 'OK' twice. Select cells H3:H134 Access conditional formatting and enter the same formula as the New Rule: =AND(C3="Avail.",H3=MAX(H:H)) Format as you wish and click 'OK' twice. Now, when a given row contains 'Avail.' in column C and the cell in column H, in the same row, is the Max value in column H, both cells in column C and H will format as you opted.