VBA Tutorial 4: String Manipulation

Topics covered in this Tutorial:

In Cell String Manipulation
VBA String Manipulation

String Manipulation

As seen in Tutorial 2, Strings are a sequence of characters which can consist of either alphabets, numbers, special characters, or all of them.

When we scrape data from websites, we will only need certain parts of it. To trim and extract these specific parts we use String Manipulation.

In Cell String Manipulation

Excel has already written codes that allow you to manipulate strings on Excel. Let’s learn about them quickly before heading over to VBA.

Common In Cell Functions:
SUM( ), AVERAGE ( ), MAX ( ), MIN ( )

String Manipulation In Cell Functions:
LEFT( ), RIGHT( ), MID( ), LEN( ), FIND( ), SUBSTITUTE( ), CONCATENATE( )

Tutorial3 01 Left — LEFT(text, num_char)

Tutorial3 02 Right — RIGHT(text, num_char)

Tutorial3 03 Mid — MID(text, start_num, num_char)

Tutorial3 05 Find — FIND(search_term, text, start_num)

Tutorial3 06 Substitute — SUBSTITUTE(text, old_text, new_text)

Tutorial3 07 Concatenate — CONCATENATE(text1, text2,…)

VBA String Manipulation

Instr( ) – Instr( ) returns the position of the first occurrence of a search term in a string

Instr(start, Str1, Search_Term)

InstrRev( ) – InstrRev( ) returns the position of the search term from the end of the string

InstrRev(Str1, Search_Term)

Mid( ) – Mid extracts string between start and end points

Mid(Str1, Start_Posn, End_Posn)

StrReverse( ) – StrReverse reverses String. “ABC” becomes “CBA”

StrReverse(Str1)

Len( ) – Outputs the number of characters in a string

Len(Str1)

Try this out!

Sub VBAStringManipulation()

    Dim Str1 As String

    Str1 = "TomCruise@gmail.com"

    Debug.Print InStr(1, Str1, "i")

    Debug.Print InStrRev(Str1, "i")

    Debug.Print Mid(Str1, 17, 3)

    Debug.Print StrReverse(Str1)

    Debug.Print Len(Str1)

End Sub