Excel Compare Substring: Step-by-Step Prefix Matching Guide

Comparing substrings in Excel is a common task when you need to analyze text data. Specifically, checking if a prefix from one column exists within the prefixes of another can be achieved using Excel formulas. This guide will walk you through a step-by-step method to compare substrings in Excel, focusing on prefix matching using built-in functions.

Extracting Prefixes from Column A

To begin, we need to extract the prefixes from the strings in Column A. We’ll define a prefix as the portion of the string before the second delimiter. In this example, we’ll use the underscore character “_” as our delimiter. The following Excel formula will extract the prefix:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

Let’s break down this formula:

• LEFT(Column_A, ...): This function extracts a specified number of characters from the beginning of the string in Column_A.
• FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)): This part locates the position of the second delimiter within the string.
  • FIND(Delimiter, Column_A): Finds the position of the first delimiter.
  • 1 + FIND(Delimiter, Column_A): Starts the search for the delimiter from the position after the first delimiter.
  • FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)): Finds the position of the second delimiter.
• - 1: We subtract 1 from the position of the second delimiter because we want the prefix before the delimiter.

By applying this formula to each cell in Column A, you’ll create a list of prefixes for each string in that column.

Comparing Prefixes with Column B Using Array Formula

Now that we have the prefixes from Column A, we can check if the prefix of each string in Column B is present in the list of prefixes from Column A. To do this, we’ll use an array formula with the OR function:

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

Note: This is an array formula, so you must enter it by pressing Ctrl + Shift + Enter after typing it in the formula bar. Excel will automatically add curly braces {} around the formula to indicate it’s an array formula.

Here’s how this formula works:

• Prefix_A: This represents the entire range containing the prefixes extracted from Column A in the previous step.
• LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1): This part extracts the prefix from the string in Column_B using the same logic as before.
• ... = Prefix_A: This compares the extracted prefix from Column B to each prefix in the Prefix_A range. Because it’s an array formula, this comparison is performed for every prefix in Prefix_A, resulting in an array of TRUE or FALSE values.
• OR(...): The OR function checks if at least one value in the array of TRUE or FALSE values is TRUE. If any prefix from Prefix_A matches the prefix from Column B, the OR function returns TRUE; otherwise, it returns FALSE.

This array formula effectively checks if the prefix of a string in Column B exists anywhere within the prefixes of Column A.

Excel substring comparison example showing formulas to check if Column B prefixes are present in Column A prefixes using LEFT and FIND functions.

By using these formulas, you can efficiently compare substrings in Excel and perform prefix matching for your data analysis needs. This method provides a flexible way to handle string comparisons based on defined delimiters and prefixes.