How do I change decimal dot into decimal comma?

Question

I have a column containing numbers with mixed decimal separators.

I need to use "," as separator.

How do I change dot into comma?

With ThisWorkbook.Worksheets("RAW").Range("A1")
    .Value = Replace(.Value, ".", ",")
End With

The point is stored as text

Is there any way to store it as general/numeric?

I tried this

With ThisWorkbook.Worksheets("RAW").Range("A1")
    .NumberFormat = "General"
    .Value = Replace(.Value, ".", ",")
    .NumberFormat = "General"
    .Value = .Value
End With

I got error: Type Mismatch :(

Carlos
– Carlos

2020-11-28 14:21:34 +00:00
Commented Nov 28, 2020 at 14:21 — Carlos
– Carlos, Commented Nov 28, 2020 at 14:21
yeah, sorry. just typo

Carlos
– Carlos

2020-11-28 14:28:47 +00:00
Commented Nov 28, 2020 at 14:28 — Carlos
– Carlos, Commented Nov 28, 2020 at 14:28
@JvdV could you please look at my answer below?

Carlos
– Carlos

2020-11-28 14:59:05 +00:00
Commented Nov 28, 2020 at 14:59 — Carlos
– Carlos, Commented Nov 28, 2020 at 14:59

JvdV · Accepted Answer · 2020-11-28 15:35:54Z

1

A single cell could be solved quick and dirty through:

.Value = Replace(.Value, ".", ",")*1

Or:

.Value = CSng(Replace(.Value, ".", ","))

If you happen to have a range to process, you could use:

Sub Test()

With ThisWorkbook.Worksheets("RAW").Range("A1:A3")
    .Replace ".", ","
    .TextToColumns
End With

End Sub

Per column:

Sub Test()

With ThisWorkbook.Worksheets("RAW").Range("C2:D4")
    .Replace ".", ","
    For Each col In .Columns
        col.TextToColumns
    Next
End With

End Sub

edited Nov 28, 2020 at 15:35

answered Nov 28, 2020 at 15:07

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Over a year ago

ok but I get error: "Microsoft Office Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only" how to iterate through multiple columns?

Collectives™ on Stack Overflow

How do I change decimal dot into decimal comma?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related