Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert text to number (Excel 2002 SP2)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to number (Excel 2002 SP2)

    Data, text to columns generally is a good tool to fix those.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert text to number (Excel 2002 SP2)

    (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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Convert text to number (Excel 2002 SP2)

    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 <alt>-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

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert text to number (Excel 2002 SP2)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Convert text to number (Excel 2002 SP2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •