VBA: How to Test if a String starts with a given character

VBA: How to Test if a String starts with certain letters

There are many ways that we can test if the string which begin or start with the certain string or letter. Some time we want to perform the action only on certain field or records that begin or start with the specified letter. For example, we want to know how many customers have first name start with letter “P”. In this How To, I will show introduce the Like comparison operator, InStr, and Left built-in function.

Like comparison operator

We will use Like along with an asterisk (*) to test if the string begins with our substring.

Syntax

“String” Like “SubString*”

Example of Like

Most of the time, we will use the If Statement to test if the string starts with the specified string. Per example of code below, we will get a message “Yep, this name begins with “Mo”” because the string is “Modesto” and the specified string “Mo*”. The string “Mo*” means it begins with “Mo” and ignores the rest of the string.

If “Modesto” Like “Mo*” Then

MsgBox “Yep, this name begins with ” & Chr(34) & “Mo” & Chr(34) & “”

End If

Other than a hard code of string, it can be refer to the any field on current form or table. Per example below, the message “Yep, this name begins with “Mo”” because “Me.City” is referring to the current City field on current form which has a value as “Modesto.”

Example of using Like in SQL for Subform

Like built-in function is widely used as a criteria for other functions. For example below, I use Like built-in function to find the category that begins with “t” that I type in the textbox. The result will display on the subform for category name that begin with t letter as shown below.

InStr built-in function

The InStr built-in function can be used to test if a String contains a substring. The InStr will either return the index of the first match, or 0.

Syntax

InStr ([start], string1, string2,[ compare ] )

start

Is Optional. Numeric expression that sets the starting position for each search.

string1

Is Required. String expression being searched.

string2

Is Required. String expression sought.

compare

Is Optional. Specifies the type of string comparison

Example:

IF Statement will be used to test if the return from InStr function is true. The InStr starts at the position 1 on string1 “Hello World” and looks for string2 “Hello W”. If InStr returns 1, it is true that the string1 (“Hello World,”) begins with the string2 (“Hello W”). A message will display “Yep, this string begins with Hello W!” as shown below. More information about InStr at MS website.

If InStr(1, “Hello World”, “Hello W”) = 1 Then

MsgBox “Yep, this string begins with Hello W!”

End If

Another example, we can use only the String1 and String2, omit the Start and Compare. It is returning True same result as using Like comparison operator above.

If InStr(Me.City, "Mo") =1 Then
MsgBox "Yep, this city name begins with " & Chr(34) & "Mo" & Chr(34) & ""
‘or do something elseEnd if

Left built-in function

The Left built-in function is the most straight forward way to implement it in VBA.

Syntax

Left(Str, Length)

Str – is Required. String expression from which the leftmost characters are returned.

Length – is Required. Integer Numeric expression indicating how many characters to return from the left of string. If zero, a zero-length string (“”) is returned.

To determine the number of characters in Str, we will use the Len function. This example demonstrates the use of the Left function to return a substring of a given String. For example below, the given string is “Hello World!” and the number of characters to return from the left of the given string. The characters “He” will return from the Left function of the given string “Hello World!”

Create a Function from a Left built-in function

For the convenience of later use, we can create a function that will return True if the string begins with the certain string or character is true. We will use the Left built-in function as a part of function. We will call this function StartsWith() and place it under the Module so we call it anywhere in this program.

Public Function StartsWith(strText As String, prefix As String) As Boolean