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( )
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!
