WS decimal separator - convert dot to comma


0. Decimal point, decimal comma, and digital grouping


Decimal separators comprise two basic types:

  1. Decimal point ( . ) and thousands separator (digital grouping: comma) - used in Australia, China, India, Malaysia, Middle East, North America, United Kingdom, ...
    • Function arguments separator: comma. =SUM(1.1,2.2,3.3)
    • Range union operator: comma. A1:A10,C1:C10 when typed in Name Box
  2. Decimal comma ( , ) and thousands separator (digital grouping: dot) - used in much of Europe (Austria, ..., Germany, ..., Ireland, ... ) South America, Indonesia, Vietnam, ...
    • Function arguments separator: semicolon. =SUM(1,1;2,2;3,3)
    • Range union operator: semicolon. A1:A10;C1:C10 when typed in Name Box

For further details see Wikipedia


1. Example


In figure 1, an Australian financial report on the left (decimal point, and thousands comma), has its numbers linked to a German financial report on the right (decimal comma, and thousands dot). Excel has File >Options > Advanced set to Use system separators (Numbers > Decimal symbol, and Numbers > Digit grouping symbol). See Windows region settings


xlf-aud-eur-ws
Fig 1: Decimal point and decimal comma - decimal point numbers with formats (left) and text numbers (right) with Euro German style. Scroll to the right to view formulas for Euro presentation


The Australian values are numbers with number formats.

  1. R10C4 (fig 1): 1234567.89 number
    • Category: Accounting
    • Decimal places: 2
    • Symbol: None
  2. R12C4 (fig 1): 679012.34 number
    • Category: Accounting
    • Decimal places: 2
    • Symbol: $
    • Keyboard shortcut: Ctrl+Shift+$ for currency format
    • VBA code for accounting format:
      Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
  3. Currency formats have:
    • the currency symbol left aligned with a space between the symbol and first number
    • a trailing space after the last number. This space accommodates the right parenthesis in negative values, subject to Windows settings

The German euro values are text numbers with punctuation characters.

  1. R10C10 (fig 1): 789.320,98 text
    • Category: General
    • Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RC[-7]*R8C4,"#,##0.00"),
      ".","#"),",","."),"#",",") & REPT(CHAR(32),3)

  2. R12C10 (fig 1): 434.126,54 € text
    • Category: General
    • Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RC[-7]*R8C4,"#,##0.00"),
      ".","#"),",","."),"#",",") & CHAR(32) & CHAR(128)

2. WS formulas


2.1 SUBSTITUTE and TEXT


WS formula #4 [R10C10] with line-breaks:

 
R10C10 =                                    nesting level ===
SUBSTITUTE(                                 0. enclosing function
    SUBSTITUTE(                             1. nested
        SUBSTITUTE(                         2. nested
            TEXT(RC[-7]*R8C4,"#,##0.00")    3. nested
        ,".","#")
    ,",",".")
,"#",",")
&
REPT(                                       0. enclosing function
    CHAR(32)                                1. nested
,3)
 

3. WS functions - syntax


WS functionDescription
Rept(text, number_times) Repeats text a given number of times
Substitute(text, old_text, new_text, [instance_num] ) Replaces existing text with new text in a text string
Text(value, format_text) Returns a value as text with a specific number format