Formula to search if text contains certain letters

Excel1991

Board Regular
Joined
Aug 1, 2018
Messages
58
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
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
Joined
Aug 23, 2010
Messages
16,397
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
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 12, 2009
Messages
1,626
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
Joined
Aug 1, 2018
Messages
58
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,355
Messages
5,547,454
Members
410,792
Latest member
Jabberwokki
Top