Decimal comma

[ReeveCZE]

Hi,

I have a sheet full of numbers, but I need to change their format to my native (that is Czech) where we use a decimal comma instead of a decimal point. Otherwise, my Excel won't add up or substract the data.

If I perfom the following code manually, it works.

Selection.NumberFormat = "0.00"
   
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

If I use this code in a Sub(), it will leave me with all the data set as "number saved as text" and the option to convert it into number.

I would really appreciate any help. Thanks in advance.

 

[Oeldere]

Did you try to change the . (dot) in a , (Comma)

 

[ReeveCZE]

Yes, that's the second part of the code. Works manually, not in a macro... it really replaces the dots with commas, but somehow messes up the cell format...

Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

 

[Oeldere]

maybe you can try this one on a dummy of your sheet.

Sub TextRangeToNumbers_incolumn_A()
'Made by Datsmart 14-07-2006 on mr Excel
    Dim Rng As Range
    Dim c As Range
'Change Column A referrence as needed
    Set Rng = Range("A1:A" & Range("A1500").End(xlUp).Row)
    On Error Resume Next
'Cycle through each item in Rng, Trim value then add zero
    For Each c In Rng
        If c.Value <> "" Then
            Application.WorksheetFunction.Trim (c.Value)    'Trim removes any spaces
            c.Value = c.Value + 0   'Adding 0 changes item to number
        Else
        End If
    Next c
End Sub

 

[ReeveCZE]

Awesome, that works! Adding 0 to each range is enough to convert it to number.

Thank you.