Formatting numbers in a column

[Larry Cohen]

I have a column of numbers in the form "123 4567890" (approximately
800 rows in this formatting). I want to change the whole column to
phone number format, but when I go to: format,cells,special,phone
number the formatting does not change.

I can individually change each row if I remove the space between the 3
and the 4 (in above example), but there must be a way to do this all
at once for all the rows in this column. Here's what I tried:

1)I inserted two new columns.
2)Under the data menu I selected text to column sub-menu and created
one column with the 123 and second with 4567890.
3)Then I went to the insert menu, function sub-menu, Text, Concatenate
and made a brand new column that was in the form "1234567890"

However, when I tried to format the cells as a phone number, the cells
stayed the same.

Can anybody help?

 

[Norman Harker]

Hi Larry!

Insert a helper column
Pre-format a cell with telephone number
Use formula:
=SUBSTITUTE(A1," ","")
You can then select the helper column numbers and
Copy
Edit > Paste Special > Values > OK

Existing numbers can now be deleted


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jim Rech]

Number formats only work with numbers. Because of the space in these
entries they are text. Select the range of text and do a Edit, Replace of
the space with nothing. This should convert them to true numbers in the
process.