Excel Formatting wont work. Urgent help required.PLEASEEEEEEEEEEEEEEEEEEEEE.

[Nikticia]

I have 2 columns in a spreadsheet that need phone numbers to be formatted to land line 00 0000 0000 and mobile 0000 000 000.

This document is an import from Practice Manager that had phone numbers in separate columns with area code and first 4 digits of mobile numbers in a separate column to the rest of the numbers.

I merged these 2 columns with the Simple Concatenation.

Now when I try to format each column to show the numbers in the correct format nothing happens to the existing data and the formula works in the empty cells within that column if I type a new phone number.

I have been pulling my hair out trying to work out why this is happening I really don't want to retype the 600+ phone numbers in each column again.

I am using Excel 2010.

Please help.

[pooja_deshpande]

After you concatenate, copy your final column and paste special values to remove formulas. Then, multiply the entire column by 1 (to convert them to a number format, after concatenation it is text).
Now apply the formatting, it will work

[Nikticia]

Hi

Thank you for replying.

I have pasted the values but not sure what you mean or how to by multiply the entire column by 1.

Sorry.

Thanks

[pooja_deshpande]

put the number 1 in any blank cell nearby. Copy this cell (containing 1), select your entire column where you have pasted values, right click and go to paste special, there in Operations, you will see "Multiply" option. Select that and press OK.

This converts text to number.

[Nikticia]

Hi

Ok I did that and formatted it and it added 00 0000 0000 to all the empty cells in that column but didnt change any of the existing numbers.

I have attached my spreadsheet if you would not mind seeing if you can fix it would be really appreciated.

Thanks

[pooja_deshpande]

please find attached...

[Nikticia]

Thank you very much.

I would like to know how you did it as it would not work for me. I can now submit my spreadsheet tomorrow.

You have been a life saver.

Is there a way to delete this thread so as these numbers are not available anyone else?

Thanks again.

[pooja_deshpande]

OK, I think you used a space in concatenation. For home numbers I first removed the spaces between the numbers (I'm talking of "within" numbers, not between two consecutive rows) and then converted them to the required format.
For Mobile numbers, there were some text entries, like names etc. This prevents "entire-column-at-once" formatting, so first moved this text data to a different cell and repeated the above procedure.

As for removing the file I'm afraid you have to contact the moderators for that. Once the edit time is over we cannot do such changes...

PS: I have managed to removed my file.

[Nikticia]

Thanks again.