Unwanted change when using "TextToColumns" twice-CodePudding

I have the following issue.

One of my sheets holds a column that contains values stored as text (e.g. value as 1,22 stored as a text). Currently I use the following Code to convert those entries to values:

Sub Convert()
    Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp)).TextToColumns DataType:=xlFixedWidth, DecimalSeparator:=","
End Sub

If I run the macro once the result is fine. However if I run to macro a second time (after I already converted the text to values with the code shown above). The numbers change their format from the comma "," as the decimal separator to a dot "." as a decimal separator (e.g. 1,22 --> 1.22).

This is a problem, because my region uses the comma.

Does anybody know how to prevent this from happening?

Kind regards Sombrero

Here Screenshots:

Original data@

After first time text2columns:

After second time text2columns:

CodePudding user response：

You original data are strings, and running TextToColumns converts them into numbers. When you run TextToColumns a second time, Excel needs to convert those numbers first into strings.

I can only guess about the internal logic of this conversion, but it wouldn't surprise me if this is not using any regional settings.

Anyhow, I would say it's not a good idea to use the TextToColumns command on numeric values. Use a filter to get only the cells containing text:

Dim r As Range
set r =  Range(Cells(2, "K"), Cells(Rows.Count, "K").End(xlUp))
On Error Resume Next     ' To prevent runtime error "No cells found"
Set r = r.SpecialCells(xlCellTypeConstants, xlTextValues)
If err.Number = 1004 then exit sub
On Error Goto 0
r.TextToColumns DataType:=xlFixedWidth, DecimalSeparator:=","