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
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),""))
Wow, that awesome thanks ever so much!! You have saved me so much time!
Reply With Quote
Bookmarks