text to numbers
 

Here's a weird one. I have a spreadsheet that was sent to me that contains
numbers as text containing dashes(-). Normally I can remove the dashes to
create a whole number. On this spreadsheet I can remove the dashes, but it
replaces them with spaces that I can't remove. I've used replace, "Trim",
"Clean"; I've tried to change the fomat to every number format, I've copied
and pasted special, all to no avail.(I even tried multipling it by 1 (VALUE)
is returned). The only thing close that worked was to use "text to column"
and then "Contantenate", but that does not pickup the zeros in the 9 digit
number (not couting spaces). Example: 011 012 012 appears as 111212 after
"Contatenate". Thanks Mike

text to numbers
 

Just a wild guess... select the affected cells and then click Edit/Replace
on Excel's menu bar. Clear the "Find what" field and then, with the cursor
still in that field, key-in Alt+0160 FROM THE NUMBER PAD ONLY (that is, hold
down the Alt key and, while it is down, type 0160 on the number pad.... yes,
type that leading 0); clear the "Replace field" and then click the "Replace
All" button.

--
Rick (MVP - Excel)


"Miked" wrote in message
...
Here's a weird one. I have a spreadsheet that was sent to me that contains
numbers as text containing dashes(-). Normally I can remove the dashes to
create a whole number. On this spreadsheet I can remove the dashes, but
it
replaces them with spaces that I can't remove. I've used replace, "Trim",
"Clean"; I've tried to change the fomat to every number format, I've
copied
and pasted special, all to no avail.(I even tried multipling it by 1
(VALUE)
is returned). The only thing close that worked was to use "text to column"
and then "Contantenate", but that does not pickup the zeros in the 9 digit
number (not couting spaces). Example: 011 012 012 appears as 111212 after
"Contatenate". Thanks Mike