VBA Function to Separate Letters from Numbers in a String

This document outlines a VBA function that separates letters from numbers in a given string. The function iterates through each character, categorizing it as either a letter or a numeric digit.

Function Overview

Function Name

SeparateLettersAndNumbers

Parameters

• inputString As String: The string that contains both letters and numbers to be separated.

Return Type

• Returns a String formatted as "Letters: {letters}, Numbers: {numbers}".

Exceptions

• Raises an error if the input string is empty.

Code Implementation

Function SeparateLettersAndNumbers(inputString As String) As String
    ' Documentation Block
    ' This function separates letters from digits in the given input string.
    ' Parameters:
    '   inputString: The input string containing letters and numbers.
    ' Returns:
    '   A string formatted as "Letters: {letters}, Numbers: {numbers}".
    ' Raises:
    '   Exception if inputString is empty.

    Dim letters As String
    Dim numbers As String
    Dim i As Integer
    Dim currentChar As String

    ' Input Validation: Check for empty string
    If Len(inputString) = 0 Then
        Err.Raise vbObjectError + 1, "SeparateLettersAndNumbers", "Input string cannot be empty."
    End If

    ' Initialize variables
    letters = ""
    numbers = ""

    ' Loop through each character in the input string
    For i = 1 To Len(inputString)
        currentChar = Mid(inputString, i, 1)  ' Get the current character

        ' Check if the character is a letter or a digit
        If IsNumeric(currentChar) Then
            numbers = numbers & currentChar  ' Concatenate to numbers
        ElseIf currentChar Like "[A-Za-z]" Then
            letters = letters & currentChar  ' Concatenate to letters
        End If
    Next i

    ' Format the result
    SeparateLettersAndNumbers = "Letters: " & letters & ", Numbers: " & numbers
End Function

Explanation of the Code

1. Documentation Block: The function is documented with an explanation of its purpose, parameters, return value, and exceptions.
2. Input Validation: The code checks if the input string is empty and raises an error if it is.
3. Variable Initialization: Two strings (letters and numbers) are initialized to store letters and numeric characters.
4. Looping Through Characters: A For loop iterates through each character in the input string.
   • It uses Mid to extract each character.
   • IsNumeric checks if the character is a number.
   • The Like operator is used to identify letters.
5. Returning Formatted Result: The function returns a formatted string with separated letters and numbers.

Code Usage Example

Here’s how you can use the SeparateLettersAndNumbers function within your VBA code:

Sub TestSeparation()
    Dim inputStr As String
    Dim result As String

    ' Define the input string
    inputStr = "abc123def456"

    ' Call the function
    result = SeparateLettersAndNumbers(inputStr)

    ' Output the result
    MsgBox result  ' Displays: "Letters: abcdef, Numbers: 123456"
End Sub

Conclusion

This VBA function provides a straightforward approach to separate letters and numbers from a string, ensuring robust input validation and proper formatting of the output. It exemplifies best practices in VBA coding and can be readily integrated into larger applications where string manipulation is required. For more advanced techniques and learning, you might consider the courses offered in the Enterprise DNA Platform.