Sub Data_Separation2()
Dim rng As Range, cell As Range
Dim myStr As String
LastRow = 21 'added this manually since its value isn't known.
'looks like you have a header in row 1?
ActiveSheet.Range("A2:A" & LastRow).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 9), Array(9, 9), Array(10, 9), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:=True
Set rng = ActiveSheet.UsedRange 'assumes there's something in row 1 so:
With Intersect(rng, rng.Offset(1)) 'to only process data body and not the headers.
.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
zz = .Value
For r = 1 To UBound(zz)
For c = 1 To UBound(zz, 2)
If Right(zz(r, c), 1) = "." Then zz(r, c) = Left(zz(r, c), Len(zz(r, c)) - 1)
Next c
Next r
.Value = zz
.NumberFormat = "0.000"
End With
End Sub
It should be