Topics covered in this Tutorial:
- In Cell String Manipulation
- VBA 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( )
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
Mid( ) – Mid extracts string between start and end points
Mid(Str1, Start_Posn, End_Posn)
StrReverse( ) – StrReverse reverses String. “ABC” becomes “CBA”
Len( ) – Outputs the number of characters in a string
Try this out!
|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)|