Excel VBA Lesson 8: Mastering String Functions in VBA

String manipulation is a fundamental skill in Excel VBA programming, essential for processing text data, cleaning inputs, and extracting specific information. This lesson covers the core string functions that will supercharge your text processing capabilities.

8.1 The InStr Function

The InStr function is your search engine within strings, returning the position of a substring. This is invaluable for:

• Data validation checks
• Text parsing operations
• Pattern recognition in strings

Example 8.1: Finding Substring Position

Private Sub cmdInstr_Click()           
  Dim phrase As String
  phrase = Cells(1, 1).Value
  'Returns position of "ual" in "Visual Basic" (4)
  Cells(2, 1) = InStr(phrase, "ual") 
End Sub

Practical Application: Use InStr to validate if specific keywords exist in user inputs or to parse structured text data like CSV files.

8.2 The Left Function

The Left function extracts a specified number of characters from the beginning of a string. Common business uses include:

• Extracting country codes from phone numbers
• Processing fixed-width file formats
• Creating abbreviations from names

Example 8.2: Extracting First Characters

Private Sub cmdLeft_Click()
  Dim phrase As String
  phrase = Cells(1, 1).Value
  'Returns "Visu" from "Visual Basic"
  Cells(3, 1) = Left(phrase, 4) 
End Sub

Pro Tip: Always combine Left with Len to avoid errors when the string is shorter than your extraction length.

8.3 The Right Function

The mirror of Left, this function extracts from the string's end. Essential for:

• File extension extraction
• Last name identification
• Processing reverse-ordered data

Example 8.3: Extracting End Characters

Private Sub cmdRight_Click()
 Dim phrase As String
 phrase = Cells(1, 1).Value
 'Returns "asic" from "Visual Basic"
 Cells(4, 1) = Right(phrase, 4) 
End Sub

Real-world Use: Extract the last 4 digits of credit card numbers for display while masking the rest.

8.4 The Mid Function

The most flexible string extractor, Mid pulls text from any position. Perfect for:

• Extracting specific data fields
• Processing complex text patterns
• Custom string parsing operations

Example 8.4: Flexible Middle Extraction

Private Sub cmdMid_Click()
 Dim phrase As String 
 phrase = Cells(1, 1).Value 
 'Returns "sua" from "Visual Basic"
 Cells(5, 1) = Mid(phrase, 3, 3) 
End Sub

Advanced Technique: Combine Mid with InStr to extract text between specific markers in a string.

8.5 The Len Function

The foundation of all string operations, Len tells you how long your string is. Critical for:

• Input validation
• Loop control
• Memory management

Example 8.5: Determining String Length

Private Sub cmdLen_Click()
 Dim phrase As String 
 phrase = Cells(1, 1).Value 
 'Returns 12 for "Visual Basic"
 Cells(6, 1) = Len(phrase) 
End Sub 

Best Practice: Always check string length before extraction to avoid runtime errors.

The output of all examples is shown below, demonstrating how these functions work together:

String Functions Cheat Sheet