Problem with VALUE()

[kingsushi001]

Good Day

I have a data feed from a supplier which I imported into excel.
I want to take their prices from the "Price" column and use VALUE
on it because it seems to be a string.

Now, VALUE works fine on all values except when the value in "Price"
is higher than 999.99 So basically I get the correct values on 0.01-999.99.
As soon as it hits 1000.00, it craps out.

Can anyone please tell me why it's doing this?

Thank you in advance

 

[Scott Huish]

It doesn't for me. Although there shouldn't be any need to use the VALUE function. You can just add 0 to the value.
=A1+0

Can you post some sample data for what is not working?

 

[inactiveuserps07]

@kingsushi001, I'm wondering if a thousands-place comma may be getting hard-coded into the text-numbers. What happens if you do this?

=SUBSTITUTE(A1,",","")+0

 

[kingsushi001]

https://drive.google.com/file/d/0B2C2ndpap_2OLXNWaldFSi1FeEE/view?usp=sharing

Here's a link to the files. I've tried everything suggested so far and everything I could find on google.
The problem is with the "Regular Price" column. It pulls the value from the "price" column which in turn
pulls it off a pivot table connected to the Esquire Feed workbook. The Esquire Feed workbook pulls it from the web.

Again, any help would be greatly appreciated.

 

[inactiveuserps07]

@kingsushi001, replace the formula in the "Regular Price" column with this one:

=VALUE(SUBSTITUTE(SUBSTITUTE([@price],"R ",""),CHAR(160),""))

Essentially, what I was thinking above was on point; but instead of the data having a hard-coded comma when numbers got into the thousands, the comma had been replaced with a non-printing character. So the formula here just deletes that non-printing character.

 

[kingsushi001]

Thank you so much. everything is working now. Had a slight problem, just had to replace the , with a ;.

Again, thank you very much

 

[inactiveuserps07]

Happy to be of help. Thanks for reporting back.