• Home
 · 
• String Manipulation
 · 
• Convert any Numerical String Format to valid numerical string and set it to be ready to be converte

Convert any Numerical String Format to valid numerical string and set it to be ready to be converte

Convert any Numerical String Format to valid numerical string and set it to be ready to be converted with CDbl or something like that.

Rate Convert any Numerical String Format to valid numerical string and set it to be ready to be converte

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

    Dim dblTstErr As Double
    Dim Ctr As Integer, Nb As Integer
    On Error Resume Next
    Nb = 0
    For Ctr = 1 To Len(Txt)
        If Mid(Txt, Ctr, 1) = "." Then
            Nb = Nb + 1
        End If
    Next
    If Nb > 1 Or Nb = 0 Then
        Nb = 0
        For Ctr = 1 To Len(Txt)
            If Mid(Txt, Ctr, 1) = "," Then
                Nb = Nb + 1
            End If
        Next
    End If
    If Nb > 1 Then
        Nb = 0
        For Ctr = 1 To Len(Txt)
            If Mid(Txt, Ctr, 1) = "," Then
                Nb = Nb + 1
            End If
        Next
        If Nb > 1 Then
            Nb = 0
            For Ctr = 1 To Len(Txt)
                If Mid(Txt, Ctr, 1) = "." Then
                    Nb = Nb + 1
                End If
            Next
        End If
    End If
    If Nb = 1 Or Nb = 0 Then
        SwitchDot = Txt
        dblTstErr = CDbl(SwitchDot)
        If Err <> 0 Then
            Err = 0
            SwitchDot = Replace(Txt, ",", ".")
            dblTstErr = CDbl(SwitchDot)
            If Err <> 0 Then
                Err = 0
                If InStr(1, Txt, ".") > 0 Then
                    SwitchDot = Replace(Txt, ",", " ")
                    dblTstErr = CDbl(SwitchDot)
                    If Err <> 0 Then
                        Err = 0
                        SwitchDot = Replace(SwitchDot, ".", ",")
                        dblTstErr = CDbl(SwitchDot)
                        If Err <> 0 Then
                            SwitchDot = "0"
                        End If
                    End If
                End If
                If Err <> 0 Then
                    Err = 0
                    If InStr(1, Txt, ",") > 0 Then
                        SwitchDot = Replace(Txt, ".", " ")
                        dblTstErr = CDbl(SwitchDot)
                        If Err <> 0 Then
                            Err = 0
                            SwitchDot = Replace(SwitchDot, ",", ".")
                            dblTstErr = CDbl(SwitchDot)
                            If Err <> 0 Then
                                SwitchDot = "0"
                            End If
                        End If
                    End If
                End If
            End If
        End If
    Else
        SwitchDot = "0"
    End If
End Function