Translate Number into String using VBA (Visual Basic for Applications)

Translate Number into String using VBA (Visual Basic for Applications)

Translate String

In this PoC I will be creating a custom or user defined function which will take string as input and translate each character into digits by adding space between. User can make change in the code to fit in his need.

Function

=TranslateDigits(Number)

  1. Number: refers to a string having numbers, needs to be translated

Code example

Public Function TranslateDigits(Number As String) As String
    Const SPACE_CHAR = " "
    If Number <> "" Or Len(Number) > 0 Then
        Dim oNum As Long
        For oNum = 1 To Len(Number)
            Select Case Mid(Number, oNum, 1)
                Case "0":
                    TranslateDigits = TranslateDigits & "Zero" & SPACE_CHAR
                Case "1":
                    TranslateDigits = TranslateDigits & "One" & SPACE_CHAR
                Case "2":
                    TranslateDigits = TranslateDigits & "Two" & SPACE_CHAR
                Case "3":
                    TranslateDigits = TranslateDigits & "Three" & SPACE_CHAR
                Case "4":
                    TranslateDigits = TranslateDigits & "Four" & SPACE_CHAR
                Case "5":
                    TranslateDigits = TranslateDigits & "Five" & SPACE_CHAR
                Case "6":
                    TranslateDigits = TranslateDigits & "Six" & SPACE_CHAR
                Case "7":
                    TranslateDigits = TranslateDigits & "Seven" & SPACE_CHAR
                Case "8":
                    TranslateDigits = TranslateDigits & "Eight" & SPACE_CHAR
                Case "9":
                    TranslateDigits = TranslateDigits & "Nine" & SPACE_CHAR
                Case Else
                    TranslateDigits = TranslateDigits & "NULL"
            End Select
        Next oNum
        TranslateDigits = Trim(TranslateDigits)
    End If
End Function

Implementation

Output

Next >> Customize Ribbon Controls in Excel with example

Leave a Reply

Your email address will not be published.