Lesson 9: Mastering String Manipulation in Excel VBA

9.1 The InStr Function: Finding Text Within Strings

The InStr function searches for a substring within a larger string and returns its position. This is particularly useful for data validation or parsing complex strings.

Example 9.1: Basic InStr Usage

Private Sub cmdInstr_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(4, 1) = InStr(phrase, "ual")
End Sub

This code searches for "ual" in the text from cell A1. For "Visual Basic", it returns 4 (the starting position of "ual").

Practical Application:

• Check if an email address contains "@" symbol
• Find specific codes within product IDs
• Validate text formats in user inputs

9.2 The Left Function: Extracting Beginning Characters

Extract a specified number of characters from the start of a string.

Example 9.2: Basic Left Function

Private Sub cmdLeft_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(2, 1) = Left(phrase, 4)
End Sub

Extracts first 4 characters ("Visu") from "Visual Basic".

Advanced Example: Extract Area Codes

Private Sub ExtractAreaCode()
Dim phoneNumber As String
 phoneNumber = "(123) 456-7890"
 Cells(1, 2) = Mid(phoneNumber, 2, 3) 'Extracts "123"
End Sub

9.3 The Right Function: Extracting Ending Characters

Get characters from the end of a string - useful for file extensions or suffixes.

Example 9.3: Basic Right Function

Private Sub cmdRight_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(3, 1) = Right(phrase, 5)
End Sub 

Extracts last 5 characters ("Basic") from "Visual Basic".

Practical Application:

Private Sub GetFileExtension()
Dim fileName As String
 fileName = "report_2023.xlsx"
 Cells(1, 2) = Right(fileName, 5) 'Returns ".xlsx"
End Sub

9.4 The Mid Function: Extracting From Any Position

The Mid function is versatile for extracting substrings from any position.

Example 9.4: Basic Mid Function

Private Sub cmdMid_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(5, 1) = Mid(phrase, 8, 3)
End Sub

Extracts "Bas" starting from position 8 (3 characters long).

Advanced Example: Parse Full Names

Private Sub ExtractMiddleName()
Dim fullName As String, space1 As Integer, space2 As Integer
 fullName = "John Quincy Adams"
 space1 = InStr(fullName, " ")
 space2 = InStr(space1 + 1, fullName, " ")
 If space2 > 0 Then
    Cells(1, 2) = Mid(fullName, space1 + 1, space2 - space1 - 1)
 End If
End Sub

9.5 The Len Function: Measuring String Length

Determine the number of characters in a string, including spaces.

Example 9.5: Basic Len Function

Private Sub cmdLen_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(6, 1) = Len(phrase)
End Sub

Returns 12 for "Visual Basic" (including the space).

9.6 The UCase and LCase Functions: Changing Case

Standardize text case for comparisons or formatting.

Example: Case Conversion

Private Sub StandardizeCase()
Dim userInput As String
 userInput = "Excel VBA"
 Cells(1, 2) = UCase(userInput) 'EXCEL VBA
 Cells(2, 2) = LCase(userInput) 'excel vba
End Sub

Practical Application:

• Case-insensitive comparisons: If UCase(input) = "YES" Then
• Formatting user inputs consistently

9.7 The Str and Val Functions: Number-String Conversion

Convert between numeric and string representations.

Example: Type Conversion

Private Sub ConvertTypes()
Dim num As Double, text As String
 num = 123.45
 text = "456.78"
 
 Cells(1, 2) = Str(num) 'Converts to " 123.45" (note leading space)
 Cells(2, 2) = Val(text) 'Converts to 456.78
End Sub

9.8 The Chr and Asc Functions: ASCII Conversion

Work with ASCII codes for special characters or control codes.

Example: ASCII Functions

Private Sub ASCIIExamples()
 'Common ASCII codes
 Cells(1, 1) = "A = " & Asc("A") '65
 Cells(2, 1) = "a = " & Asc("a") '97
 Cells(3, 1) = "Tab = " & Asc(vbTab) '9
 
 'Special characters
 Cells(4, 1) = Chr(169) '©
 Cells(5, 1) = Chr(174) '®
End Sub

9.9 Combining String Functions

Real-world applications often combine multiple string functions.

Example: Extract Domain from Email

Private Sub ExtractEmailDomain()
Dim email As String, atPos As Integer
 email = "[email protected]"
 atPos = InStr(email, "@")
 If atPos > 0 Then
    Cells(1, 2) = Mid(email, atPos + 1)
 End If
End Sub

Example: Clean and Format Data

Private Sub CleanData()
Dim rawInput As String, cleanOutput As String
 rawInput = "   Mixed CASE input 123   "
 
 'Trim spaces, proper case first letter, lowercase rest
 cleanOutput = UCase(Left(Trim(rawInput), 1)) & _
               LCase(Mid(Trim(rawInput), 2))
 
 Cells(1, 2) = cleanOutput 'Returns "Mixed case input 123"
End Sub

Summary of Key Points

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial