Currency values downloaded to excel do not convert to numbers

[tonyhTonyh199]

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

[#2]

@tonyhTonyh199 

Try replacing "EUR " (without the quotes) with nothing.

[#3]

@tonyhTonyh199 

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))

[#4]

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.

[#5]

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! message

[#6]

@tonyhTonyh199 

I 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?

[#7]

---

@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.

---

@tonyhTonyh199 

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.