How to Find Repeating Row Patterns with Wildcards in Excel

When each row contains one letter, the task is a substring search across a vertical sequence. In Excel 365, join the letters into one string, generate every possible starting position, test a pattern such as ACA or A?A, and return the corresponding values from column A.

How the Pattern Search Maps Back to Row Numbers

If B2:B301 contains one character per row, character 1 in the joined string corresponds to A2, character 2 to A3, and so on. The question-mark wildcard represents exactly one character; avoid the asterisk when you need a fixed-length sequence.

Return Every Matching Starting Row in Excel 365

1. Enter the pattern in E2, for example ACA or A?A.
2. In F2, enter =LET(data,TEXTJOIN("",TRUE,B2:B301),p,E2,n,LEN(p),starts,SEQUENCE(LEN(data)-n+1),FILTER(INDEX(A2:A301,starts),ISNUMBER(SEARCH(p,MID(data,starts,n))),"No match")).
3. Press Enter. Excel spills every matching starting value from column A.
4. Test a known pattern at the beginning, middle, and end of the data and confirm that overlapping matches are returned.

Use a Helper Column for Easier Auditing

For a three-character pattern, enter =B2&B3&B4 in C2 and fill down only to the last complete window. In D2 use =IF(ISNUMBER(SEARCH($E$2,C2)),A2,""). Filter out blanks to see all starting rows. Change the helper formula when the pattern length changes.

Avoid False Matches from Blank or Multi-Character Cells

The compact LET formula assumes one character per row. Clean spaces and blanks first, and validate that no cell contains multiple letters. Otherwise the joined character position no longer maps one-to-one to the row number in column A.

Find Row Patterns with Helper Formulas in WPS Spreadsheets

Use Word, Excel, and PPT for FREE

Free Download

WPS Office can perform this local spreadsheet task directly. WPS Spreadsheets supports common XLSX formulas and a practical helper-column method that makes each tested text window visible.

1. Open a duplicate workbook and enter the search pattern, such as A?A, in E2.
2. For a three-letter pattern, enter =B2&B3&B4 in C2 and fill down through the last complete window.
3. In D2 enter =IF(ISNUMBER(SEARCH($E$2,C2)),A2,"") and fill down.
4. Filter column D to hide blanks, then compare each returned row with the source letters.

WPS AI can assist with formula explanations and data summaries. Confirm function availability in your installed version before replacing a complex production workbook.

Find Repeating Row Patterns with Wildcards in Excel FAQs

What does the question-mark wildcard match in Excel SEARCH?

A question mark matches exactly one character, so A?A matches ACA, ABA, or ADA but not AA or ABBA.

Can the formula return overlapping patterns?

Yes. Because it tests every starting position, a sequence such as AAAAA can return overlapping matches for AAA.

Why are the returned row numbers offset?

The formula assumes the first joined character is B2 and returns the corresponding value from A2. Change both source ranges together if your data begins elsewhere.

Can I search for several patterns at once?

Run the formula for each pattern in a separate cell or create a named LAMBDA and apply it to a list of patterns with MAP in supported Excel 365 builds.