Searching and Replacing Strings

On This Page

When processing strings, we often need to search for a specified substring within a given string or replace it with another string once found. This section introduces methods for string searching and replacement.

Searching Strings

[Excel VBA] In Excel VBA, use the InStr function to return the position of the first occurrence of one string within another. Syntax:

code.vba 
InStr([Start,] String1, String2[, Compare])

Start (optional): Starting position for the search (default: 1, the first character).

String1: The original string.

String2: The substring to search for.

Compare (optional): Comparison mode (vbBinaryCompare for case-sensitive, vbTextCompare for case-insensitive; default: vbBinaryCompare).

Example: Find the first occurrence of "VB" in "Hello VBA". Sample file: Samples\ch06\Excel VBA\SearchReplace.xlsm.

code.vba 
Sub Test()
    Dim strA As String, strB As String, intP As Integer
    strA = "Hello VBA"
    strB = "VB"
    intP = InStr(strA, strB)  ' Returns 7(position of "VB")
    Debug.Print intP
End Sub

Use InStrRev for reverse search (from the end of the string).

[Python] Use find() (first occurrence), rfind() (last occurrence), count() (number of occurrences), startswith() (check prefix), or endswith() (check suffix). Positions are 0-based.

code.python 
>>> s = 'HePyllo Python'
>>> s.find('Py')    # First occurrence at index 2 → 2
>>> s.rfind('Py')   # Last occurrence at index 8 → 8
>>> s.count('Py')   # Number of occurrences → 2
>>> 'abcab'.startswith('abc')  # True
>>> 'abcab'.endswith('ab')    # True

Replacing Strings

[Excel VBA] Use the Replace function to replace a substring. Syntax:

strR = Replace(Expression, Find, ReplaceWith[, Start[, Count[, Compare]]])

Expression: Original string.

Find: Substring to replace.

ReplaceWith: Replacement substring.

Start (optional): Starting position for replacement.

Count (optional): Number of replacements (default: all).

Compare (optional): Case sensitivity (vbBinaryCompare/vbTextCompare).

Example: Replace "VBA" with "Python" (case-insensitive). Sample file: Samples\ch06\Excel VBA\SearchReplace.xlsm.

code.vba 
Sub Test2()
    Dim strA As String, strC As String
    strA = "Hello VBA"
    ' Case-sensitive (no replacement)
    strC = Replace(strA, "vBa", "Python")
    Debug.Print strC  ' Hello VBA
    ' Case-insensitive (replacement occurs)
    strC = Replace(strA, "vBa", "Python", , , vbTextCompare)
    Debug.Print strC  ' Hello Python
End Sub

[Python] Use the replace() method. Syntax:

str.replace(str1, str2, num)

str: Original string.

str1: Substring to replace.

str2: Replacement substring.

num (optional): Maximum number of replacements (default: all).

Example: Replace "a" with "w" (max 5 times).

code.python 
>>> 'abcababcababcab'.replace('a', 'w', 5)  # 'wbcwbwbcwbwbcab'
>>> 'abcababcababcab'.replace('a', 'w')     # 'wbcwbwbcwbwbcwb' (all replaced)