Today, I thought I’d go back to the basic and create a simple article to go over text parsing techniques and illustrate how we can use a few built-in function to get what we want from a string.

If you are new to VBA and text parsing, you’re probably asking yourself: which functions?  Well, in this article I will touch upon:

• Len
• Right
• Left
• Mid
• InStr
• InStrRev
• Trim
• LTrim
• RTrim

So let’s get started

Len()

Len stands for Length and is used, you guessed it, to return the length of the passed string.

So

Len("What’s your name?")

returns a value of 17

Right()

Right is used to return a specified number of characters starting from the end of the string.

So

Right ("What’s your name?", 5)

returns “name?”

Left()

Inversely, Left is used to return a specified number of characters starting from the beginning of the string.

Thus

Left ("What’s your name?", 6)

returns “What’s”

Mid()

Mid is a nifty function that enables you to parse a section of a string by specifying the starting position and number of characters to return.  If you omit the number of characters argument, it goes to the end of the string.

For instance

Mid ("What's your name?", 8)

returns “your name?” – start at the 8^th character all the way to the end

Mid ("What's your name?", 13)

returns “name?” – start at the 13^th character all the way to the end

Mid ("What's your name?", 8, 4)

returns “your” – start at the 8^th character but only return 4 characters

InStr()

InStr is a function used to locate an element within a string starting from the left-hand side, and return the position if it is found, otherwise it return 0.

InStr("What's your name?", " ")

returns 7 – the space is the 7^th character in this string from the left-hand side

InStrRev()

InStrRev is a function used to locate an element within a string starting from the right-hand side (reverse of InStr), and return the position if it is found, otherwise it return 0.  It always important to know that the position number always starts from the 1^st character on the right.

InStrRev ("What's your name?", " ")

returns 7 – the firth space from the right-hand side of the string is the 12^th character in that string.

Trim()

Trim remove both leading and trailing spaces from a string

Trim("    What's your name?     ")

returns “What’s your name?”

LTrim()

LTrim remove only the leading spaces from a string

LTrim("    What's your name?    ")

returns “What’s your name?    ”

RTrim()

RTrim remove only the trailing spaces from a string

RTrim("    What's your name?    ")

returns “    What’s your name?”

Using These Functions to Effectively Parse Strings

The real power of these functions becomes apparent when you start using them together.

It’s always a question of identifying a pattern and then using these function to break the string based on that pattern.

Real Life Example

Say you have a field containing a person’s full name, first name and last name, and would like to split them into 2 distinct fields to properly normalize your data (as you should), how could you do this using the functions listed above?

Get Someone’s First Name

Left("Elon Musk", InStr("Elon Musk", " ") - 1)

returns Elon. Don’t forget the -1 to the length to be returned as we use InStr to locate the position of the space, but we don’t want to include the space in the result.

Alternate solutions could include:

Trim(Left("Elon Musk", InStr("Elon Musk", " ")))

Or

Mid("Elon Musk", 1, InStr("Elon Musk", " ") - 1)

So there are a multitude of ways to get the same information (even more than I have shown)

Get Someone’s Last Name

Similarly, to extract the person’s last name we could do something along the lines of:

Right("Elon Musk", Len("Elon Musk") - InStrRev("Elon Musk", " "))

Or

Right("Elon Musk", Len("Elon Musk") - InStr("Elon Musk", " "))

Or

Mid("Elon Musk", InStr("Elon Musk", " ") + 1)

What About A 3^rd Element?

What if there was a salutation in the full name: “Mr. Elon Musk”?

Salutation

Left("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") - 1)

First Name

This one’s a little trickier.

Left(Replace("Mr. Elon Musk", Left("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") - 1) & " ", ""), InStr(Replace("Mr. Elon Musk", Left("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") - 1) & " ", ""), " ") - 1)

Or

Mid("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") +1, len(Mid("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") +1)) - instrrev(Mid("Mr. Elon Musk", InStr("Mr. Elon Musk", " ") +1), " "))

Last Name

Right("Mr. Elon Musk", Len("Mr. Elon Musk") - InStrRev("Mr. Elon Musk", " "))

As you can see, extracting first and last elements is easy, but with each additional component things get more and more complicated to extract ‘inside’ elements.  This is where using a function like Split() becomes critical.  Keep reading to learn more.

Like with most things in life, the more you work with these functions the easier the process becomes.

What About Working With Queries?

The above has always been more along the lines of VBA, but the same is true in a Query.  You can simply substitute the “Elon Musk” portions with your Field as these functions are available via the query expression builder.

So say in a query you have a field name FullName and you want to create 2 new fields FirstName and Last Name well then we could do, amongst many other variations:

SELECT FullName, Left([FullName],InStr([FullName]," ")-1) AS FirstName, Mid([FullName],InStr([FullName]," ")+1) AS LastName
FROM [YourTableName];

So the exact same expressions can be used universally!

Using Split and Other Non-Accessible Functions in Queries

Depending on the data you are working with, sometimes parsing can be made easier with other existing functions, or even with a custom function, that aren’t available to you (not built-in).

One prime example of this would be the Split() function which would be ideal for parsing name components from a string.  The problem being that Split() isn’t available in queries, right?  Wrong.

Luckily for us there is a very simple way to fix this which I covered in my article:

Once we have this setup we could then simply do:

SELECT FullName, String_Split([FullName],0," ") AS FirstName, String_Split([FullName],1," ") AS LastName
FROM [YourTableName];

And for the case with Salutation it simply becomes:

SELECT FullName, String_Split([FullName],0," ") AS Salutation, String_Split([FullName],1," ") AS FirstName, String_Split([FullName],2," ") AS LastName
FROM [YourTableName];

One simple function permits us to extract the n^th element from any string by simply changing the index input argument.

Other Resources on the Subject

Len function (Visual Basic for Applications)

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Right function (Visual Basic for Applications)

Returns a Variant (String) containing a specified number of characters from the right side of a string.

Left function (Visual Basic for Applications)

Returns a Variant (String) containing a specified number of characters from the left side of a string.

Mid function (Visual Basic for Applications)

Returns a Variant (String) containing a specified number of characters from a string.

InStr function (Visual Basic for Applications)

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

InStrRev function (Visual Basic for Applications)

Returns the position of an occurrence of one string within another, from the end of the string.

LTrim, RTrim, and Trim functions (Visual Basic for Applications)

Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).