CODEWITHSUNDEEP
excelvbaexcel-formula
10101
10101

Reputation: 2412

Custom number (price) format independent of localization

I am wondering is it possible to have custom number format using Excel formula that will not be dependent on localization of Excel application (EU/US)?

For example I have value 1291660.

Then using formula =TEXT(A1;"# ##0,00"). I get as an output 1 291 660,00. The target is to have in any case 1.291.660,00 as an output. Any Excel professional to give an advice?

I have tried =TEXT(A1;"#.##0,00") - This didn't work

I think VBA is the only solution to this. I have found my old question about the same topic, but it seems that solution provided is not working for some reason?

Ultimate 1000 separator using VBA

Function CustomFormat(InputValue As Double) As String
    Dim sThousandsSep As String
    Dim sDecimalSep As String
    Dim sFormat As String
    
    sThousandsSep = Application.International(xlThousandsSeparator)
    sDecimalSep = Application.International(xlDecimalSeparator)
    
    ' Up to 6 decimal places
    sFormat = "#" & sThousandsSep & "###" & sDecimalSep & "######"
    
    CustomFormat = Format(InputValue, sFormat)
    If (Right$(CustomFormat, 1) = sDecimalSep) Then
        CustomFormat = Left$(CustomFormat, Len(CustomFormat) - 1)
    End If
    
    ' Replace the thousands separator with a space
    ' or any other character
    CustomFormat = Replace(CustomFormat, sThousandsSep, " ")
End Function

By replacing CustomFormat = Replace(CustomFormat, sThousandsSep, " ") with CustomFormat = Replace(CustomFormat, sThousandsSep, ".") output is .1 291 660

Upvotes: 1

Views: 132

Answers (3)

JvdV
JvdV

Reputation: 75900

You may use:

=SUBSTITUTE(SUBSTITUTE(FIXED(A1,2,0),",","."),".",",",INT(LEN(A1)/3)+1)

The way it works is that on an EU-system FIXED() will return: 1.291.660,00 but on an US-system it should return 1,291,660.00. To create the same output-string, we can SUBSTITUTE() all comma's to dots. A 2nd SUBSTITUTE() will then replace only the last dot back to a comma. To find the right index I used INT(LEN(A1)/3)+1 which works well on itegers like 1291660. If you happen to have decimal values, you can change this to:

=SUBSTITUTE(SUBSTITUTE(FIXED(A1,2,0),",","."),".",",",INT(LEN(INT(A1))/3)+1)

EDIT:

The above should always return the desired format, but it's a string. To return the numeric value in any further calculations, you can use NUMBERVALUE():

=NUMBERVALUE(C1,",",".")

Upvotes: 1

z32a7ul
z32a7ul

Reputation: 3787

You don't need VBA for this. You can use SUBSTITUTE to replace the default separator characters, and you can detect what these are by cutting them out from the formatted string of a known number. I use ASCII 1 (SOH) character to avoid replacing twice (e.g. replacing thousands separator from " " to ".", than replacing decimal separators from "." to "," would cause that thousands separators appear as ","):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(1234567.89,"# ##0.000"),MID(TEXT("# ##0",1000),2,1),CHAR(1)&" "),MID(TEXT("0.0",0.1),2,1),CHAR(1)&","),CHAR(1)&" ","."),CHAR(1)&",",",")

This will output "1.234.567,890".

This output will appear as a string (you cannot add numbers to it, and it is left adjusted by default), and you cannot change this behavior if you don't use Excels local settings for separators.

BTW, using " " for thousands separator and either "." or "," for decimals is the clearest way of displaying numbers.

Upvotes: 1

AnilGoyal
AnilGoyal

Reputation: 26238

Go to excel file tab, click options and then the following options as desired

enter image description here

Uncheck use system separators and define your own

Upvotes: 1

Related Questions