Nov 06 2022 05:55 AM
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
Nov 06 2022 06:03 AM
Try replacing "EUR " (without the quotes) with nothing.
Nov 06 2022 06:06 AM
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))
Nov 06 2022 07:30 AM
Nov 06 2022 07:32 AM
Nov 06 2022 08:38 AM - edited Nov 06 2022 08:44 AM
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?
Nov 06 2022 10:31 AM
@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: