Japanese currency custom format

[Jubinell]

Hi,

I would like to set the format of a cell to the Japanese currency format. This means that the comma separator occurs at 4-digit interval instead of 3 (ten-thousand separator instead of thousand separator) like this:

1,000 -> 1000
10,000 -> 1,0000
1,000,000 ->100,0000
1,000,000,000 -> 10,0000,0000

and so on...

There are some rules with the decimals, but I will leave that out for now.

Anybody knows how to achieve this with either custom format or VBA, instead of initiating system changes?

 

[Blade Hunter]

There may be a better way but this should do what you want. It will place the Yen formatted value in the cell to the right of the activecell. You need to have the value you want to convert selected in excel before running the code.

Sub Yen()
Dim YenValue As String
Dim YenOld As String
Dim X As Integer
Dim YenLength As Integer
If IsEmpty(ActiveCell.Value) Then End
YenOld = ActiveCell.Value
YenLength = Len(ActiveCell.Value)
If YenLength / 4 = Int(YenLength / 4) Then PartCount = Int(YenLength / 4) Else PartCount = Int(YenLength / 4) + 1
    For X = 1 To PartCount
        YenValue = "," & Right(YenOld, 4) & YenValue
        If X < PartCount Then YenOld = Left(YenOld, (Len(YenOld) - 4))
    Next
YenValue = "¥" & Right(YenValue, Len(YenValue) - 1)
ActiveCell.Offset(0, 1).Formula = YenValue
End Sub

 

[Jubinell]

Thanks for the codes...a quick test-run shows that it works. However the Vietnamese folks at giaiphapexcel.com have beaten you guys to a solution this time

They suggest a custom format:
[>999999999999]¥####","####","####","####;[>99999999]¥####","####","####;####","####

where then yen signs are optional.

 

[Blade Hunter]

Thats pretty cool, I fiddled with the custom number format but couldn't get it, Creds to those guys. It would also be advisable to use a format instead as you are not actually changing the value, My method formats as a string which would basically render the number completely useless for any calculations.

 

[Blade Hunter]

On a number over 16 chars, both error:

Changing my code from .value to .formula when populating the variables set mine working again.

YenOld = ActiveCell.Formula
YenLength = Len(ActiveCell.Formula)

¥1,0000,0000,0000,0000 vs ¥10000,0000,0000,0000

again though, mine would still be a string and fairly unusable.

 

[Jubinell]

Well, it proves it again and again that technology + language = win. I ran this question through English, French, Japanese, Vietnamese Excel forums to see what kind of responses I would get. =p

~Usually Mr. Excel wins though.

 

[Blade Hunter]

Hmmmm, on further investigation, put 1000 in there or less and the formatting goes screwy. You have to get to 1000000 before the formatting method works correctly.

 

[Jubinell]

Hmmmm, on further investigation, put 1000 in there or less and the formatting goes screwy. You have to get to 1000000 before the formatting method works correctly.

I played around with it a little...the reason for this it seems it that the custom format only specifies rules for 2 range of numbers, between 9x12 and 9x8, and between 9x8 and 9x4. What's worse is that that's all that Excel allows you two specify with custom formats: 2 at max! I've tried to put in more and Excel tells me it can't use it.

Solution:
Combine custom format with conditional format
For cells in question, create 4 conditional format rules:
1) For numbers greater than 9x12, use the custom format ¥####","####","####","####

2) For numbers between 9x12 and 9x8, use the custom format ¥####","####","####

and so forth...

Since custom formats cannot work with numbers greater than 10^12 (I think), these 4 rules are all that we can do.