Geek asks and answers

Excel dollar sign text does not convert to number

When I enter Euro or Sterling values ​​in an excel sheet, they are automatically recognized as numbers / currency and are right-aligned, and I can also extract their value.

eg. £ 10 is automatically leveled to the right. Using the formula =VALUE(A1)returns 10, as follows. This also worked when using the € symbol. However, if I used the $ character, the value is recognized as text and using the above formula's results #VALUE!. Perhaps this is due to my regional / language settings of Windows, which are intended for the UK, and not for the United States.

EDIT: I have two records in accounting formats: £ English (UK) $ English (USA)

+5
source share
3 answers

I figured out a job for those who have a similar problem. I used =VALUE(REPLACE(A1,1,1,""))to remove the dollar sign and simply get the value of the number, then the number can be formatted using the accounting format "$ English (USA)".

+4
source

Alternatively for @Matt's answer, if your currency sign is not the first character or has a random position in the text, you can use =SUBSTITUTE(A1,"$","").

+8
source

$ - , $A$10. . .

+2
source

More articles:


All Articles