VBA & Macro Problems

Filter Excel Cells Containing Multiple Text Values with VBA

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 870 views

Question details

Filter rows where a cell contains at least one of several specified text values.

Filter Excel Cells Containing Multiple Text Values with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to show rows containing PTE, LTD, PRIVATE, LIMITED, or LLP without relying on an AutoFilter array of wildcard patterns.
Observed behavior
The desired result is a filtered list of rows whose cells contain at least one of the specified text values.
Before you start

Identify the column containing the text and make sure the filter range has a header row. The examples below assume text is in column A, headers are in row 1, and an available helper column is column E.

Solution 1Recommended

Use a Helper Column and Filter for Matching Text

Use this approach to filter multiple text patterns without writing VBA.

SEARCH checks for each value anywhere in the cell and is not case-sensitive. OR returns TRUE when at least one value is found.

1
Add a helper header

Enter a header such as Match in cell E1.

2
Enter the matching formula

In E2, enter =OR(ISNUMBER(SEARCH("PTE",A2)),ISNUMBER(SEARCH("LTD",A2)),ISNUMBER(SEARCH("PRIVATE",A2)),ISNUMBER(SEARCH("LIMITED",A2)),ISNUMBER(SEARCH("LLP",A2))).

3
Fill the formula down

Select E2 and drag its fill handle down through the last row of data. Change A2 in the formula if your text is in a different column.

4
Filter the results

Select the data range including the helper column, choose Data > Filter, and use the Match header dropdown to show only TRUE.

Use a Helper Column and Filter for Matching Text
Substring matches: The formula matches text anywhere in a cell, including inside a longer word. It does not require all five values to be present.
Filter text in WPS Spreadsheets

Filter Multiple Text Values in WPS Spreadsheets

Use a helper formula and the filter dropdown in WPS Spreadsheets to find rows containing any of the specified text values, without needing a macro.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the column containing the text.
  2. 2. Add a helper formula: Enter Match in E1 and enter =OR(ISNUMBER(SEARCH("PTE",A2)),ISNUMBER(SEARCH("LTD",A2)),ISNUMBER(SEARCH("PRIVATE",A2)),ISNUMBER(SEARCH("LIMITED",A2)),ISNUMBER(SEARCH("LLP",A2))) in E2, adjusting A2 to your text column.
  3. 3. Fill down: Drag the fill handle from E2 to the last data row.
  4. 4. Show matching rows: Select the data range including the helper column, choose Data > Filter, and select TRUE from the Match column's filter dropdown.
Find multiple text patterns with one helper formulaOpen and edit Microsoft Excel XLSX and XLS filesFilter matching rows without relying on VBAUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula match all five values or just one?

It returns TRUE when a cell contains at least one value. To show those rows, filter the helper column for TRUE.

Is the text search case-sensitive?

No. SEARCH is not case-sensitive, so it matches values such as LTD, Ltd, and ltd.

How do I add another text value to the filter?

Add another ISNUMBER(SEARCH("value",A2)) argument inside the OR formula in the helper column, fill the revised formula down, and reapply the TRUE filter.

What should the VBA Field number be?

Use the helper column's position within the range passed to AutoFilter. For example, column E is Field 5 in A1:E100, but Field 4 in B1:E100.

Why does a longer word appear in the results?

SEARCH finds a text value anywhere within a cell, including inside a longer word. The helper formula is not limited to whole-word matches.