2

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?

2 Answers 2

5

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.

Read more about number formats here.

0

Hi i got a solution which is a quick fix, can try this can try 0"."0,"万"

1
  • Your answer could be improved if you edited it it to note the relevant implications of this approach. For example, 1. doesn't change the underlying number which may be useful for calculations. 2. Only displays down to the thousands digit therefore the number 499 would be displayed as any of the following depending on the number of decimal places shown: 0.0万 or 0.0.5万 or 0.0.50万 or 0.0.499万.
    – Blindspots
    Commented Feb 20 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.