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

    Value function (Excel 2000)

    I've inherited a spreadsheet which has all of the numbers somehow are being interpreted as text. I've tried formatting and I've tried the value function. I've also tried to use the trim function since there are leading spaces in the cells. Nothing seems to work. I've got 6 years of data. Too much to reenter. Any suggestions would be appreciated.

    I've attached a snippet of the spreadsheet.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Value function (Excel 2000)

    There seems to be a spurious "blank " character embedded in your data. It is however not the standard blank (Chr(32)) but rather Chr(160). You can search and replace this character with "". To enter the character in the Find what text box use Alt 0160 (hold down the Alt key and press 0160).

    Or run the following code :

    Sub CleanUp()
    ActiveSheet.UsedRange.Replace _
    What:=Chr(160), Replacement:=""
    End Sub

    Andrew C

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

    Re: Value function (Excel 2000)

    That explains why trim and value didn't work.

    Can you explain how you diagnosed this so I could do it myself in the future?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Value function (Excel 2000)

    I used the CODE function which returns the ASCII code of the first character in a string.

    =CODE(C2) returned 160.

    So did =CODE(RIGHT(C2,1)), indicatiing that the cell value had leading and trailing 'spaces'


    Andrew C

Posting Permissions

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