# Formula to search if text contains certain letters

#### Excel1991

##### Board Regular
Hi All,

I am helping build a spreadsheet for some teachers that will assist in reducing time it takes them to grade children's spelling. This is primarily related to children who do not speak english as a primary language.

I was trying to find a formula that basically does the following:
1. Kids are asked to spell the word "shine"
2. The formula needed would need to assess whether the word contains the letter "i" and the letter "e". The trick is that the letter "i" must come before the letter "e" for the kids to earn a point.
3. As this is primarily designed for children learning English, it does not matter which letter(s) fall in between the "i" and the "e" as the lessons are teaching them how english letters work together with spelling being the secondary function. .
1. For instance, if the child were to spell "shipe" instead of "shine", they would still receive a point for using the correct two letter (i and e)
Is it possible to use a formula to perform this task? I was thinking it would need to be a formula structured similar to something like this:

=IF(ISNUMBER(SEARCH(b1,a1)),1,0) where b1 is the letters im looking for ("i" followed by "e") and a1 is the actual word the child spelled.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Marcelo Branco

##### MrExcel MVP
Maybe something like this

Pasta1
ABC
1WordLettersResult
2shipei*e1
3shepii*e0
4siepi*e1
5shinei*e1
6shenii*e0
7iei*e1
Plan2
Cell Formulas
RangeFormula
C2:C7C2=IF(ISNUMBER(SEARCH(B2,A2)),1,0)

Observe the asterisk between the two letters - it means 0 or more characters

Hope this help

M.

#### Yongle

##### Well-known Member
where are you going with this?
Is EVERY test searching for 2 letters in a specific order

Very similar to solution posted by @Marcelo Branco

In B2
i*e
In C2
=NOT(ISERROR(MATCH("*"&B1&"*",A1,0)))*1

#### Misca

##### Well-known Member
You're close: Just add the asterisks ("*") between the letters you're searching for and it should work.

The asterisk is a wildcard meaning "zero or more digits". Another wildcard symbol is the question mark. It is used to mark a single digit.

#### Excel1991

##### Board Regular
Thank you all very much! Using the asterisk did the trick.

Nice to come across a neat little trick. Thank you again for your help

Replies
1
Views
59
Replies
16
Views
262
Replies
10
Views
112
Replies
11
Views
753
Replies
4
Views
81