Number formatting for Europe

[dracer]

Hi there. I'm trying to reformat a number as follows. 101,015.15 should show as follows 101.015,15 with the decimal and the comma reversed. I was able to change the first comma to a decimal with custom number formatting, but can't seem to change the second period. Can someone send me the code if possible? Thanks!

 

[Marcelo Branco]

Maybe

Try it in a copy of your workbook

Select the cells (just one column) and run this macro

Sub aTest()
    Dim rCell As Range
    
    With Selection
        For Each rCell In .Cells
            rCell.Value = Replace(Replace(rCell, ",", ""), ".", ",")
        Next rCell
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, FieldInfo:=Array(1, 1)
        .NumberFormat = "#,##0.00"
    End With
End Sub

Hope this helps

M.

 

[dracer]

Thanks. What it's doing is adding the comma before the last 2 digits, but it didn't insert a period in the place of the thousands. Eg. It's showing 100115,32 instead of 100.115,32 or 2541,14 instead of 2.541,14.

 

[Marcelo Branco]

It worked for me

Before macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Values​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101,015.15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2,541.14​

[/TD]
[/TR]
</tbody>[/TABLE]


After macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Values​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101.015,15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2.541,14​

[/TD]
[/TR]
</tbody>[/TABLE]


Don't know why it's not working for you

M.

 

[dracer]

I don't know. What was the format of your numbers before? I had it set to currency with 2 digits.

 

[Marcelo Branco]

Maybe there are extraneous characters (spaces?) in your data.
See if this new version works

EDITED version

Sub aTestV2()
    Dim rCell As Range
    
    With Selection
        For Each rCell In .Cells
            rCell.Value = Replace(Replace(Replace(rCell, ",", ""), ".", ","), " ", "")
        Next rCell
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=[COLOR=#ff0000]False[/COLOR], _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
        .NumberFormat = "#,##0.00"
    End With
End Sub

M.

 

[Fennek]

Hi,

these formats are controlled with
- application.decemalSeparator
- application.thousandseparator
(maybe spelling mistakes)

If you want to send your xl-workbook to Europe, just do nothing. They will open with local settings and see the right format.

If you want to create a report with Euope number formating, the best might be to change temporarily the settings.

regards

 

[Marcelo Branco]

Hi,

these formats are controlled with
- application.decemalSeparator
- application.thousandseparator
(maybe spelling mistakes)

If you want to send your xl-workbook to Europe, just do nothing. They will open with local settings and see the right format.

If you want to create a report with Euope number formating, the best might be to change temporarily the settings.

regards

I'm guessing the data were extracted (copy/paste) from somewhere where the numbers use US-Style and need to be converted.

M.

 

[dracer]

Still not sure. There are no extra characters. I just typed in a number. It's doesn't seem to want to put the comma there. From the code above it looks like you're replacing a comma with a blank. Maybe it something on the replace coding line?

 

[dracer]

Thanks for this info. I will inform my colleague