Results 1 to 3 of 3

Thread: Issue with formating text exported from an online database

  1. #1

    Issue with formating text exported from an online database



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone, I am having trouble with excel data that has been exported from an online Database into Excel. The problem is that the data that is imported should be dollar values i.e. $20,000.00 however excel has exported them in as 'General' formatting even though they show up as $20,000.00. Even after I unlock the cell, press 'CTRL+`' and change formatting to 'Number' excel still won't sum the values and continues to count them as 'Text'. The AutoCount function is turned on. Please help as this is super frustrating and I'm unable to manipulate any of the data. Is there a macro that will do this? or perhaps something that I haven't thought of that one of you lovely guru's may be able to help me with. Thanks so much! I have attached a test sample of the data
    Attached Files Attached Files

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Weird...there seems to be an invisible digit between the dollar sign and the first number that screws things up. You can tell it's there, because if you put =MID(A2,2,1) into cell B2, it returns a blank. And if you put =CODE(B2) into cell C2, it returns 63, which is the character that is applicable to a question mark.

    Don't know what that's about. But here's how to fix it...put this in cell B2 and copy down:
    =VALUE(SUBSTITUTE(A2,MID(A2,2,1),""))

  3. #3
    Wow, that awesome thanks ever so much!! You have saved me so much time!

Tags for this Thread

Posting Permissions

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