+ Reply to Thread
Results 1 to 6 of 6

numbers appearing in raw data won't convert to an Excel value

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    2016
    Posts
    605

    numbers appearing in raw data won't convert to an Excel value

    I've attached a worksheet illustrating a problem I'm having getting Excel to recognize a column of "numbers" as they appeared in an original .pdf document which I exported to Excel.

    I've tried TRIM, CLEAN, NUMBERVALUE, and have also tried removing commas and $ signs, but nothing works. I've also tried substituting the 0 for another 0 in the event the "number" in column G is the letter "O." But none of these work either.

    Interestingly, if I manually change one of the zeroes in column G to a "1", then Excel recognizes the entire number as a value. I can manually change other digits in column G, so I really don't think it is an image.

    Any thoughts?

    Thanks Much.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    224

    Re: numbers appearing in raw data won't convert to an Excel value

    You had extra spaces at the end of the 1040 in G1. Click on G1 and in Formula bar go to the end and get rid of the extra spaces at the end.

    For G2 do you really want -540? Take the - out and it works.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,413

    Re: numbers appearing in raw data won't convert to an Excel value

    You've got quite a bit going on there, this works for those examples.
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,"$",""),CHAR(160),""),UNICHAR(8208),"-")+0

  4. #4
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,237

    Re: numbers appearing in raw data won't convert to an Excel value

    There are white space #160 and the minus is hyphens #8208

    Please try
    =--SUBSTITUTE(SUBSTITUTE(G1,UNICHAR(8208),"-"),CHAR(160),)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    2016
    Posts
    605

    Re: numbers appearing in raw data won't convert to an Excel value

    Thanks Fluff and Bo! As always, I appreciate your help.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,413

    Re: numbers appearing in raw data won't convert to an Excel value

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert numbers appearing as a date into actual date in order to sort data
    By Santa1986 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2015, 12:56 PM
  2. Replies: 21
    Last Post: 01-06-2015, 11:23 AM
  3. Replies: 8
    Last Post: 06-28-2014, 03:26 PM
  4. [SOLVED] Convert numbers stored as text to numbers for range of data that changes
    By excel2425 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 01:57 PM
  5. Replies: 2
    Last Post: 04-14-2006, 12:25 PM
  6. Convert numbers stored as text to numbers Excel 2000
    By Darlene in forum Excel General
    Replies: 6
    Last Post: 01-31-2006, 04:10 PM
  7. [SOLVED] How to convert Excel imported numbers from text to numbers?
    By Alden in forum Excel General
    Replies: 9
    Last Post: 04-01-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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