Convert text to number (Excel 2002 SP2)

Topic

New Reply

Hello,

I frequently have to download financial statements various from various websites and in various formats. Sometimes the numbers really are numbers and there is no problem. Frequently though the numbers are text and even after I use the Clean() function, I still can’t do arithmetic. Is there an easy way to change text to numbers? The length varies, etc.

I’ve seen lots of date fixes, but no general number fix.

Thanks

Reply | Quote

Replies

Data, text to columns generally is a good tool to fix those.

Reply | Quote

Data, text to columns generally is a good tool to fix those.

Reply | Quote

(Only in Excel 2002 and higher)

If you select a range of cells in which the active cell is a number seen as text, Excel will display a smart tag “Number stored as text”, with Convert to Number as an option.

Reply | Quote

(Only in Excel 2002 and higher)

If you select a range of cells in which the active cell is a number seen as text, Excel will display a smart tag “Number stored as text”, with Convert to Number as an option.

Reply | Quote

Another thing to watch for is embedded “sticky spaces” (ASCII 160) . Some accounting programs will add “sticky spaces” to ensure that the numbers do not get reformatted. Excel knows to “trim” off regular spaces but treats “sticky-spaces” like regular text so many of the “conversion tricks” will not get rid of them.

Many times just find -0160 (hold alt key and type (no quotes): “0160” on numeric keypad) and replace with “nothing” [leave replace box “blank”] will get excel to automatically convert them.

Steve

Reply | Quote

Steve’s solution worked great. Thanks. It was a little tricky since I’m using my laptop, so I had to use ALT-CNTRL and the blue numbers on the keyboard.

The text to column wouldn’t work because I already have lots of columns, and I wasn’t able to get the smart tags to come up. The smart tag has worked in the past.

WOPR is a wonderful community. Thanks for all help.

Reply | Quote

With lots of columns another trick is to put the number one (1) in a blank cell. Copy it to the clipboard and then highlight the range of “Number-text” you want to be numbers, and paste special – multiply.

This will do similar to the text to columns but do more than 1 column at a time.

Neither of these tricks (Paste special – multiply, text-to-columns) work very well, if the text has “sticky spaces” for the reasons I mentioned earlier.

Steve

Reply | Quote

With lots of columns another trick is to put the number one (1) in a blank cell. Copy it to the clipboard and then highlight the range of “Number-text” you want to be numbers, and paste special – multiply.

This will do similar to the text to columns but do more than 1 column at a time.

Neither of these tricks (Paste special – multiply, text-to-columns) work very well, if the text has “sticky spaces” for the reasons I mentioned earlier.

Steve

Reply | Quote

Steve’s solution worked great. Thanks. It was a little tricky since I’m using my laptop, so I had to use ALT-CNTRL and the blue numbers on the keyboard.

The text to column wouldn’t work because I already have lots of columns, and I wasn’t able to get the smart tags to come up. The smart tag has worked in the past.

WOPR is a wonderful community. Thanks for all help.

Reply | Quote

Another thing to watch for is embedded “sticky spaces” (ASCII 160) . Some accounting programs will add “sticky spaces” to ensure that the numbers do not get reformatted. Excel knows to “trim” off regular spaces but treats “sticky-spaces” like regular text so many of the “conversion tricks” will not get rid of them.

Many times just find -0160 (hold alt key and type (no quotes): “0160” on numeric keypad) and replace with “nothing” [leave replace box “blank”] will get excel to automatically convert them.

Steve

Reply | Quote