Receive new articles directly in your inbox!! Register your email address

Excel compare strings with VBA

Ever find yourself in the world of Excel compare strings? It's a common task when merging data lists. Typically, you'd use an integer as a key for linking and apply functions like VLOOKUP. But what if there's no shared integer? In such cases, text values become the key to associating elements between the lists

In this post, we'll guide you through using the INSTR and MID functions to compare two data lists based on text values. The goal? Create a final list merging elements from both original lists seamlessly.

List 1 combines customer names and surnames in the same cell, and their fruit preferences.

Meanwhile, List 2 switches the order of name and surname in the same cell and includes the city of residence.

The output? A final list in which each customer is paired with both their city of residence and preferred fruit.

Let's dive in!

HIGHLIGHTS

1 INSTR function

2 MID function

3 VBA Code

1 INSTR function

The INSTR function is available only in VBA It allows to check if a test string contains a substring. It returns the starting position of the substring in the original string

The INSTR function has the following syntax:

Start = This is the position from which the function begins to search. For example if the Start value is set to 2 and we are searching fro "p" in the string "purpose" the output is 4

String1 = Is the string within which the substring is to be found. If we are looking for "p" in the string "purpose", then String 1 is "purpose"

String2 = Is the substring to be found in String1. If the character "p" is to be found in the word "purpose" String2 is "p"

Compare = This is the type of comparison to be made The different types of comparison are

vbBinaryCompare: It is a case sentive search of the substring If the letter "p" is to be found in the word "purpose" it

will return 1. If the letter "P" is to be found in the word "purpose" it will return 0

vbTextCompare: It is a case insensitive search. If the letter "P" is to be search in the string " purpose" it will return 1

vbDatabaseCompare: It is used for comparison with a Micorsoft Access DataBase

2 MID function

The MID function returns a substring from a string based on the start and end position.

For example, if we have the string "oranges" in cell C3, the function MID(C3,2,5) will return the substring "range"

The MID function can also be used in VBA and has the following syntax:

String = Is the text string from which you want to extract the substring

Start As Long = The starting position of the substring

Length = The number of characters of the substring

3 VBA Code

The code will generate a final result of a list called "Output" with the customer surname in the first column and the associated fruit and city of residence in the second and third column

The customer surname is taken from List 2.

The code starts with a loop running through all the rows

For k = 2 To 5

In Column E a blank space separates the customer surname from his name

The INSTR function will identify the blank space (i.e. the blank string") and return its position.

Variable i stores the position of the blank space in column E

i = InStr(1, Cells(k, 5).Value, " ", vbTextCompare)

Once the position of the blank space has been identified, it is used to extrapolate the substring of the customer surname from column E

The MID function is used by extrapolating from column E the part of text starting from position 1 and length i-1. The length is i-1 as i is the position of the blank space and we want to extract the text ending one position before the blank space

Variable src hosts the customer surname extrapolated from column E

src = Mid(Cells(k, 5).Value, 1, i - 1)

The next step is to compare the customer surname contained in variable src with the customer surname in column B

It is again a comparison between two strings and therefore the INSTR method will be used. The vbTextCompare method has been used as List 1 column B has lower and upper cases whilst List 2 column E has only upper cases

m = InStr(1, Cells(k, 2).Value, src, vbTextCompare)

If the two strings are equal ( m > 1) the data in Columns C and F will reported in columns I and J respectively.

If m > 0 Then

Cells(k, 8) = src

Cells(k, 9) = Cells(k, 6)

Cells(k, 10) = Cells(k, 3)

Else: End If

The loop continues to the next row

Next k

For your convenience the full code is reported below.

Sub textcomparison()