Forum Discussion
Currency values downloaded to excel do not convert to numbers
Hello. I have downloaded our inventory database from Zohobooks to Excel. The inventory prices are reflected as currency eg EUR 10.00. However I cannot get this field to convert to a value. Please help
6 Replies
Does the field appear literally as "EUR 10.00"? And are all of the currencies Euros?
If that is the case, a simple formula can convert the numeric portion to value and the formatting as currency can display it as desired. I've attached a demo spreadsheet. But here's the formula
=VALUE(RIGHT(A1,LEN(A1)-4))
- Thanks Mathetes,
This is how the amounts appear:
EUR 147.00
EUR 94.10
EUR 168.27
EUR 1.65
EUR 21.70
I have tried your formula but it returns a #VALUE! messageI have tried your formula but it returns a #VALUE! message
Not sure what to say, other than that I could not reproduce your #VALUE error message. I tried to add in other (blank) spaces in the text, but the formula still worked. And when I copied your more compete set of examples, and then just copied the original formula down to the remaining adjacent cells, it converted them all without error.
Given Microsoft's description of how to correct for a #VALUE message, there are a couple of things you should check.
Did you copy the formula from my sample worksheet, or re-enter it? And were you putting the formula (edited appropriately to refer to the correct column) into a column adjacent to the problem cells?
I've reattached with the more extensive set of raw data.
There also may be something about the way your text fields are formatted... Is it possible for you to post a copy of your spreadsheet on OneDrive or GoogleDrive, granting us access via a link posted here?
Try replacing "EUR " (without the quotes) with nothing.
- Thanks Hans. I have 2000 + items that have downloaded. I cannot change each line item manually and I cannot figure out how to remove the EUR with a formula.
tonyhTonyh199 wrote:
Thanks Hans. I have 2000 + items that have downloaded. I cannot change each line item manually and I cannot figure out how to remove the EUR with a formula.You can replace in one go:
- Select the entire range with amounts in EUR.
- Press Ctrl+H to activate the Replace dialog.
- Type EUR followed by a space in the Find what box.
- Leave the Replace with box empty.
- Click Replace All.
