How to convert strings in '0,000 yen' format to numbers in Excel or Spreadsheets
In Excel or Spreadsheets, you cannot calculate totals when values are in the following format:
This is because values like "12,000 yen" are interpreted as strings, not numbers.
It's like saying "Tokyo + Hokkaido = ?".
Therefore, you need to convert "12,000 yen" and similar values into numbers using functions.
I will explain using a Spreadsheet example.
There are slight differences between Excel and Spreadsheets, which I will explain as we go along.
Table of Contents
Conclusion
For Spreadsheets:
Combine the replacement function "REGEXREPLACE()" and
the number conversion function "VALUE()" as follows:
=VALUE(REGEXREPLACE(B2,"[^0-9]",""))Please change the "B2" part as appropriate.
This uses regular expressions to remove non-half-width numbers and then converts the result to a number.
As a result, "12,000 yen" has changed to the numerical value "12000".
The total could also be calculated.
For Excel
Excel does not have the "REGEXREPLACE()" function, so
use the "SUBSTITUTE()" function to remove "yen" and "," and then convert to a number with VALUE().
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"円",""),",",""))Please change the "B2" part as appropriate.
For full-width numbers
Consider a case like this:
Process by converting full-width characters to half-width using the "ASC()" function first.
=VALUE(REGEXREPLACE(ASC(B2),"[^0-9]",""))Please change the "B2" part as appropriate.
It converted to a number, and the total could also be calculated.
For Excel
The previous function works for both full-width and half-width numbers.
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"円",""),",",""))
Comments
If you found a different behavior in your environment, please feel free to leave a comment.
If this was helpful, we appreciate your support!
Your support will be used for childcare.
Or support us by buying something from the buttons below
(You don't have to buy the linked product.)
Amazon
Rakuten Ichiba
Yahoo! Shopping
PR