String to Number Conversion Excel VBA (Visual Basic for Applications)

String to Number Conversion Excel VBA (Visual Basic for Applications)

String to Number

Sometimes user may in need where he needs to perform string to number conversion to manipulate the data in excel. Unfortunately there is no inbuilt function in Excel. Therefore, I am taking it a step ahead and trying to build one PoC which includes a User Defined Function using VBA.

Code

Public Function StringToNumberConversion(oInputString As String) As String
    StringToNumberConversion = ""
    On Error GoTo errh
    If oInputString <> "" Or Len(oInputString) > 0 Then
        Dim oArr
        oArr = Split(oInputString, " ")
        For Each num In oArr
            Select Case LCase(num)
                Case "zero":
                    StringToNumberConversion = StringToNumberConversion & 0
                Case "one":
                    StringToNumberConversion = StringToNumberConversion & 1
                Case "two":
                    StringToNumberConversion = StringToNumberConversion & 2
                Case "three"
                    StringToNumberConversion = StringToNumberConversion & 3
                Case "four":
                    StringToNumberConversion = StringToNumberConversion & 4
                Case "five":
                    StringToNumberConversion = StringToNumberConversion & 5
                Case "six":
                    StringToNumberConversion = StringToNumberConversion & 6
                Case "seven":
                    StringToNumberConversion = StringToNumberConversion & 7
                Case "eight":
                    StringToNumberConversion = StringToNumberConversion & 8
                Case "nine":
                    StringToNumberConversion = StringToNumberConversion & 9
                Case ".":
                    StringToNumberConversion = StringToNumberConversion & "."
                Case Else
                    StringToNumberConversion = StringToNumberConversion & "null"
            End Select
        Next num
    End If
errh:
    If Err.Number <> 0 Then
        Exit Function
    End If
End Function

Implementation

Output

Next >> Convert Number to String a UDF in Excel VBA

Leave a Reply

Your email address will not be published.