View Full Version : [SOLVED] Searching for one of many text strings in a cell based on a text string lookup table

01-28-2014, 03:45 PM
Hello and thanks for taking the time to read this. I will try my best to explain what I'm trying to do.

I want to search a user inputted text string for a variety of strings. That user inputted text string will be stored in one cell.

Sometimes users mistype words or use different words that mean the same thing, example: "1st" or "First". I plan to manually build my own library of text variations to check for.

If I want to check the user input string for the word "first", I'll point it to the table/worksheet I've built called "1st", and the formula would check the user inputted text for any variation (based on my table/worksheet supplied definitions) of the word "1st" and return a value of true or 1 if at least on variation is present and 0 or false if none are present.

I'd like to be able to do the same thing for the word 2nd in a different cell.

I've attached an example worksheet to hopefully help. Feel free to fire away with questions. Can it be done with native excel formulas or would I need to write VBA code? I'm pretty sure I can write the VBA code on my own if needed, but native excel functions are preferable.


01-28-2014, 04:38 PM
Greetings NukedWhale,

I think I may actually have come up with a decent formula!:fainted:

By example, for {2nd;2 nd;second;scond;secnd} existing in A1:A5 of sheet '2nd', and the values being looked at in Column A of the sheet 'Destination Worksheet'!, I think this should work:

=SUMPRODUCT(--(ISNUMBER(SEARCH('2nd'!$A$1:$A$5,'Destination Worksheet'!A2,1)))*1)

for a cell in row 2, and dragged down.

See attachment to see if that is what you wanted. It should count every occurrence.

Hope that helps,


PS. If that works as nicely as it seems to, XLD's persistence in patiently answering questions has overcome the thickness of my skull. :thumb:bow: If not...:crying:

01-28-2014, 04:40 PM
Oops. Forgot attachment...

01-28-2014, 08:16 PM
XLD's persistence in patiently answering questions has overcome the thickness of my skull. :thumb:bow: If not...:crying:

Attach file 101 Room 43 Tower of London.

P.S .Don't be late

01-29-2014, 03:58 PM
First off, Thanks! This does EXACTLY what was asked. I knew that an Excel Guru on this forum would find a way to do it with sumproduct. (I tried researching sumproduct before posting here, but wasn't able to figure it out)

Now here's a potentially challenging/impossible follow up. Can the range of my search table, referenced in your code snippet as:

'2nd'!$A$1:$A$5 work as a non-fixed range? I ask because I was playing in the example workbook and when I deleted an item out of my search table, the formula would return an incorrect result. Is there an easy way to get around having to update the range in the formula every time I add or delete something from the search table?

Just so you know, I'm anticipating that the answer is "no" without using VBA or writing something very complex. That answer is perfectly acceptable. You've already been a huge help.

01-29-2014, 05:10 PM
Alas, my "Snoopy dance" comes to an end. Oh well, I was pretty tickled for a bit.


I just thought of something simple. Couldn't you use a Named Range?

01-30-2014, 03:59 AM
Why not ?

Sub M_snb()
for j=1 to 2
sn = Sheets(choose(j,"1st","2nd")).Columns(1).SpecialCells(2)

For jj = 2 To UBound(sn)
Application.AutoCorrect.AddReplacement sn(jj, 1), sn(1, 1)
End Sub

01-31-2014, 01:56 PM
Good stuff. I'm marking this as solved. Thank you for the help!