I know you can format thousands and millions by using a comma in the custom number format. However, I would like to format by ten thousand (man/wan in Asian counting system). For example 10,000,000 => 1000만 or 1000万. Does anyone know if this is possible?
Add a comment
|
1 Answer
I have two solutions, and both aren't great. The first problem is that Google Sheet has no support for that counting system. It can only shorten a number by thousands, as fractions, or as scientific notation.
First solution - We can take advantage of the % since it expands the value by 100. We use the , (comma) to shorten the number to the millions and add on the %, giving us this: 0,,"万"%. This works, but it does have the unfortunate side effect of the value becoming a percentage, which probably won't affect calculations, and including a percentage in the cell. I could not figure out how to hide it.
Second Soluion - We use a separate columnto display the wan. Multiply the value by 100 and using the format 0,,"万", we display the correct value BUT the underlying value is incorrect. I think it might actually be better to simply format it into a string using a formula such as =CONCAT(FLOOR(A1/10000),"万") just so that no mistakes can be made further along the line.
All of this of course does not account for 亿 and it's Korean variant (sorry, I don't know much about Korean). Maybe in the future Google will add a way to shrink numbers with more control. But for now, these are what I came up with. Maybe someone else will have a much more fitting solution.
