Custom Number Format With Multiple Type

[muhammad susanto]

hi all.
how to make formula to custom number format like this below:

Book1
	G	H
2	data	target/after formula
3	7.500.000	Rp7,50
4	500.000	Rp0,50
5	12.730.000	Rp12,73
6	250.456.000	Rp250,45
7	1.450.630.000	Rp1.450,63
8	12.879.700.000	Rp12.879,70
9	155.755.000.000	Rp155.755,00
Sheet1

my number in Indonesian format.
any helps, thank in advance..
.sst

 

[diddi]

i would have expected excel to just convert that in the format currency option. but if it does not, i suspect the issue may be the extra "." in the numbers, whch would make excel think they are strings, not numbers.

just see what happens if you convert them using something like this

=SUBSTITUTE(G3,".","")/10000

 

[muhammad susanto]

hi thanks but not my expected..
i mean use Custom Format Cell like 0,00##,## etc....

 

[diddi]

just use normal currency format now. no need for custom

 

[Rick Rothstein]

I am not sure which would be appropriate for your locale, so if the first Custom Format does not work correctly, then try the second one...

"Rp"#,##0.00,,

"Rp"#.##0,00,,

 

[muhammad susanto]

I am not sure which would be appropriate for your locale, so if the first Custom Format does not work correctly, then try the second one...

"Rp"#,##0.00,,

"Rp"#.##0,00,,

hi Rick, not work for both

 

[Rick Rothstein]

Maybe change the trailing commas to dots??? In my US system, each of those trailing commas have the effect of dividing by 1000... I don't know if using commas for that purpose is universal or not.

 

[muhammad susanto]

hi rick, i have do what your suggestion, but still not work..

 

[Rick Rothstein]

The problem is I cannot test my solutions because my Excel and locale settings are different from you, so I tried guessing. The first pattern I posted in Message #5 worked for me using my US settings (dot for decimal, comma for thousands separator and commas at the end for each "division" by 1000), that is all I can tell you. I would think a variation of it should have worked for you, but your tests seem to indicate that is not the case. I'm really not sure what else I can try. Hopefully someone with an Excel version and locale setting comes along to help you.