VBA Function to Separate Letters from Numbers

This document describes a VBA function that separates letters and numbers from a given string. The function returns two separate strings: one containing all letters and the other containing all numbers.

Function Overview

Purpose

• To extract letters and numbers from a user-defined string.

Parameters

• inputStr (String): The string from which letters and numbers are to be separated.

Return Type

• Returns a Variant Array:
  • First element (String): Contains all letters from the input string.
  • Second element (String): Contains all numbers from the input string.

Exceptions

• Raises an error if the input is not a string.

Code Implementation

Function SeparateLettersAndNumbers(inputStr As String) As Variant
    ' This function separates letters and numbers from an input string.
    
    Dim letters As String
    Dim numbers As String
    Dim i As Long
    
    ' Input Validation: Checking if the input is a string
    If Not VarType(inputStr) = vbString Then
        Err.Raise vbObjectError + 9999, "SeparateLettersAndNumbers", "Input must be a string."
    End If
    
    ' Initialize output variables
    letters = ""
    numbers = ""
    
    ' Loop through each character in the input string
    For i = 1 To Len(inputStr)
        If Mid(inputStr, i, 1) Like "[A-Za-z]" Then
            ' If the character is a letter, append to letters string
            letters = letters & Mid(inputStr, i, 1)
        ElseIf Mid(inputStr, i, 1) Like "[0-9]" Then
            ' If the character is a number, append to numbers string
            numbers = numbers & Mid(inputStr, i, 1)
        End If
    Next i
    
    ' Return the letters and numbers in an array
    SeparateLettersAndNumbers = Array(letters, numbers)
End Function

Explanation of the Code

• Input Validation: The function checks if the provided input is a string. If not, it raises a custom error.

• Looping through Characters: It uses a loop to iterate through each character of the string:

  • Mid(inputStr, i, 1) extracts each character.
  • Separating Logic: A conditional check determines if the character is a letter or a number using pattern matching with Like operator.

• Appending Results: The relevant characters are appended to either the letters or numbers string.

• Returning Results: Finally, the function returns an array containing both separated strings.

Code Usage Example

Here’s how to use the SeparateLettersAndNumbers function:

Sub TestSeparateLettersAndNumbers()
    Dim inputString As String
    Dim result As Variant
    Dim letters As String
    Dim numbers As String
    
    ' Example input string
    inputString = "abc123xyz456"
    
    ' Call the function
    result = SeparateLettersAndNumbers(inputString)
    
    ' Store output in variables
    letters = result(0)
    numbers = result(1)
    
    ' Display the results in a message box
    MsgBox "Letters: " & letters & vbCrLf & "Numbers: " & numbers
End Sub

Explanation of the Example

• The TestSeparateLettersAndNumbers subroutine demonstrates usage by providing an example input string.
• Results are fetched and displayed using a message box, showing both letters and numbers extracted from the input.

Conclusion

This function effectively separates letters from numbers in a given string and adheres to best practices in coding, including input validation and comprehensive documentation. For further learning and advanced data analysis techniques, consider exploring the courses available on the Enterprise DNA platform.