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 04 Len
LEN(text)
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!

Next: Tutorial 5 Web Scraping Pt 1

Previous: Tutorial 3 Intermediate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s