Vrftjkry

I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B. The words are always exactly six characters and the letters can be either upper- or lower-case.

A collection of Excel cells each contain a sentence in which one or more such words will be found (always at least one). For example:

• My Custom location is INNSA1


• I work at INMAA4


• I want to go to INDEL6


• INNSAB IS BOARDER LOCATION

The word (or words, if more than one), may be in any position in the sentence. The target words will always be separate words, not part of a larger word.

For each sentence (i.e., cell), I want to search for the presence of any word fitting the described letter pattern, and return this word as a value.

This is just an example that you can adapt to your schema.

With data from A2 to A26, in B2 enter:

=IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"")

and copy downward:

As you see, each item of "good" data is marked with a simple sequential index.

There are many ways to do each piece of the problem, but the combination of requirements points to one approach that is simple and straightforward, or a VBA solution. The non-VBA solution uses helper columns. The layout of your spreadsheet is unknown, as are other factors, like the number of sentences and the possible number of results, and how you need and want to use the results. So this answer will be more generic guidance than a specific solution.

Using functions, it will be complicated to search the sentence for potentially more than one target. A simpler approach is to break the sentence into words and then check the words. A simple way to parse the sentences is with the built-in Convert Text To Columns wizard.

Assuming the sentences are in column A, leave the next few columns blank for your results. That way, your results will always be in a known location, visible next to your data. I'll assume a maximum of two, so columns B and C will contain result values.

Figure the maximum number of words there can be in a sentence, and reserve that many subsequent columns for helper columns. These will be used to test each word. Lets say you allow for five words. Columns D:H will contain formulas pointing to columns I:M, respectively.

Lets say row 2 is your first data row, so the first formula will be in D2, pointing to I2. The formula will be similar to what Gary's Student suggested:

=IF(AND(LEFT(I2,2)="IN",OR(RIGHT(I2,1)="B",RIGHT(I2,1)="1",RIGHT(I2,1)="4",RIGHT(I2,1)="6")),1,0)

An alternate formula would be:

=IFERROR((LEFT(I2,2)="IN")*(SEARCH(RIGHT(I2,1)," 416b")>1),0)

That just gives you a couple of ideas about how to tell whether the target word fits the pattern. The second formula uses SEARCH (because it isn't case-sensitive), to see if the last character is in your list. Testing the first two characters for IN, and the last character, each result in True or False, which Excel treats as 1 and 0, respectively. So if both tests are true the result is 1, otherwise 0.

In the SEARCH expression, I started with a blank and test for a result >1. The reason I did this is because I used MS Office Online to test it, and it thinks the rightmost character of an empty cell is found in position 1 of the test string.

If SEARCH does not find a match, it returns an error, so wrapping the formula in IFERROR ensures that any word not matching results in 0.

So either formula returns 1 if the word fits your pattern, or 0 if it doesn't (or if its target cell is empty).

Enter either formula in D2, and drag or copy it across to H2, and down to the last row you need.

To fill the target cells, select all the data in column A, and copy and paste it into column I (Text to Columns overwrites the data and starts in the data column). Select the data in column I. On the Data tab, in the Data Tools group, click Text to Columns. You'll get a wizard that is self-explanatory.

Tell it the data is delimited, and select space as the delimiter. It will give you a preview of how it thinks you want it to parse the words. When you finish the wizard it will put each word in a consecutive cell in the row.

Your formulas in D:H should show at least one 1 in each row. Those are your words, and their column is in the same relative position as the parsed word.

To get the first word's value in column B, use INDEX and MATCH to find the first 1 in the row in D:H and retrieve the corresponding value from the same row in I:M. For example, B2 would be:

=INDEX(I2:M2,MATCH(1,D2:H2,0))

This retrieves the value from the cell in I2:M2 corresponding to where an exact match to 1 was found in D2:H2.

To get the subsequent values for column C (and beyond if there are more than two), there are a number of solutions to find the Nth matching 1. Here is a link to one method.