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



Rick Rothstein

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question