Mastering Excel VBA String Manipulation

Introduction:

In this guide, we’ll explore essential string manipulation techniques in Excel VBA. By adding these tools to your VBA skill set, you can effectively work with text data in your Excel macros. We’ll cover joining strings, extracting substrings, reversing text, converting to proper case, and counting words within a selected range.

Join Strings:

Use the ‘&’ operator to concatenate strings. Example:

Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"
MsgBox text1 & " " & text2

Join Strings

Left Function:

To extract the leftmost characters from a string, use the “Left" function. Example:

Dim text As String
text = "example text"
MsgBox Left(text, 4)

Left

Right Function:

To extract the rightmost characters from a string, use the “Right“ function. Example:

MsgBox Right("example text", 2)

Right

Mid Function:

To extract a substring starting in the middle of a string, use the “Mid" function. Example:

MsgBox Mid("example text", 9, 2)

Mid

Len Function:

To get the length of a string, use the "Len" function. Example:

MsgBox Len("example text")

Len

Instr Function:

To find the position of a substring in a string, use the “Instr" function. Example:

MsgBox Instr("example text", "am")

Instr

Separate Strings:

Use a loop to separate strings. This is particularly helpful when you need to split a single cell into multiple columns based on a delimiter like a comma. Example:

Dim fullname As String, commaposition As Integer, i As Integer

For i = 2 To 7
    fullname = Cells(i, 1).Value
    commaposition = InStr(fullname, ",")
    Cells(i, 2).Value = Mid(fullname, commaposition + 2)
    Cells(i, 3).Value = Left(fullname, commaposition - 1)
Next i

Separate Strings Result

Reverse Strings:

Use a loop to reverse strings. Example:

Dim text As String, reversedText As String, length As Integer, i As Integer

text = InputBox("Enter the text you want to reverse")
length = Len(text

For i = 0 To length - 1
    reversedText = reversedText & Mid(text, (length - i), 1)
Next i

MsgBox reversedText

Enter Text

Reverse String Result

Convert to Proper Case:

Convert text to proper case, where the first letter of each word is in uppercase, and the rest are in lowercase. Example:

Dim rng As Range, cell As Range

Set rng = Selection

For Each cell In rng
    If Not cell.HasFormula Then
        cell.Value = WorksheetFunction.Proper(cell.Value)
    End If
Next cell

Convert to Proper Case in Excel VBA

Convert to Proper Case Result

Count Words:

Count the number of words in a selected range, assuming one or more spaces separate words. Example:

Dim rng As Range, cell As Range
Dim cellWords, totalWords As Integer, content As String

Set rng = Selection
cellWords = 0
totalWords = 0

For Each cell In rng
    If Not cell.HasFormula Then
        content = cell.Value
        content = Trim(content)
        If content = "" Then
            cellWords = 0
        Else
            cellWords = 1
        End If
        Do While InStr(content, " ") > 0
            content = Mid(content, InStr(content, " "))
            content = Trim(content)
            cellWords = cellWords + 1
        Loop
        totalWords = totalWords + cellWords
    End If
Next cell

MsgBox totalWords & " words found in the selected range."

Count Words in Excel VBA

Count Words result

These string manipulation techniques are invaluable when working with text data in Excel VBA. They allow you to join, extract, reverse, convert, and analyze text efficiently, enhancing your ability to automate tasks involving strings.