How to automatically change invalid fields to numbers?

Question

Google Sheets

It happens that a person incorrectly filled out the table or parsed badly and the numerical value shows as text in the format 1 123.32 and you need to manually change a lot to 1123.32, etc. How can this process be automated?

Answer 1

well, or a macro
The code must be inserted into a new module

Public Function SanitData(ByRef rng As Range) As Double
   Dim data As String
   Dim ret As Double
   
   data = SanitizeNumbers(CStr(rng.Value))
   
   SanitData = IIf(Len(data) > 0, CDbl(data), 0)
End Function

Private Function SanitizeNumbers(ByVal data As String) As String
    Dim pos As Long
    Dim char As Long

    data = Replace(data, ".", ",")
    pos = 1
    Do While pos <= Len(data)
        char = Asc(mid$(data, pos, 1))
        If Not (Chr(char) Like "[0-9,]") Then
            data = Replace(data, Chr(char), "")
            pos = pos - 1
        End If
        pos = pos + 1
    Loop
    SanitizeNumbers = data
End Function

Call macro on photo