I got a table of numbers from an email, but for some reason I'm unable to convert them to numbers and use them in formulas without the dreaded #VALUE! error. The problem seems to be associated with leading spaces, but I've tried all the usual ways to get around that including:
The only thing that seems to work is manually deleting the leading spaces or re-typing the numbers. For this small data set, I just went ahead and manually deleted the leading spaces, but it's really bugging me that I can't figure out what is causing this strange behavior and the next data set may be much bigger. I've tried searching the forum and Google, but most of those just link to a more basic version of this problem (like, "Use Value()").
Small file is on dropbox.
Thanks in advance for helping clear up the mystery.
- Adding +0
- Multiplying * 1
- Number format
- VALUE()
- NUMBERVALUE(,".",",")
- TRIM()
- CLEAN()
- NUMBERVALUE(TRIM(CLEAN()),".",",")
The only thing that seems to work is manually deleting the leading spaces or re-typing the numbers. For this small data set, I just went ahead and manually deleted the leading spaces, but it's really bugging me that I can't figure out what is causing this strange behavior and the next data set may be much bigger. I've tried searching the forum and Google, but most of those just link to a more basic version of this problem (like, "Use Value()").
Small file is on dropbox.
Thanks in advance for helping clear up the mystery.