Formula to search if text contains certain letters

[Excel1991]

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.

 

[Marcelo Branco]

Maybe something like this

Pasta1
	A	B	C
1	Word	Letters	Result
2	shipe	i*e	1
3	shepi	i*e	0
4	siep	i*e	1
5	shine	i*e	1
6	sheni	i*e	0
7	ie	i*e	1
Plan2

Cell Formulas
Range	Formula
C2:C7	C2	=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]

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]

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]

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