Finding consecutive letters in a column

What function do I use to find parts of words in a column that contains both text and numbers? For example, the word "Appetiser" may be in B120, but as you can see it is spelled wrong, so I would set up the search for "App".

I want something that is NOT case sensitive. Therefore, if someone typed it in as APPETIZER, or appetizer, I want it to return a value for finding "APP" or "app" in the column. Preferably, a count of the number of times it finds it would be preferred. However, when I used:

COUNTIF(B1:120,"App")

I get a value of 0 because "app" is not the exact value that was entered. For some reason, SEARCH and FIND aren't working for me.

I would like to get either a true/false return when it finds an "app", or more preferably, a count of the number of times it finds "app" in the same column, even if the "app" is part of another word.

Thank you!

EDR

And

You two rock! Both ways are working, thank you very much!

Now, what if I want to search for two values in the same cell and count the times it occurs?

For example, PASTA 1, may be entered as PASTA1 (without the space, accidentally), and may also be mispelled.

How would I do it so I can search for "PA", and "1" in the same cell, and then return the number of times that may occur in the column (or array)?

Is there a way to build the AND function into the previous formulas you guys showed me?

Thanks so much! EDR

Thank you and Debug

Hi Weasel:

Thanks again!

I'm using the following formula exactly:

=SUMPRODUCT(IF(ISNUMBER(FIND("PA",LOWER(C8:C91),1)),1,0),IF(ISNUMBER(FIND("1",C8:C91,1)),1,0))

Even though cell C50 definitely contains the phrase "PASTA 1", the above formula is returning a value of 0. Any ideas, on what I'm mising?

I've triple checked it.

Actually, now that I look at it, the original formula is not working either:

=SUM(IF(ISNUMBER(FIND("Pas",LOWER(C8:C91),1)),1,0))

C50 = PASTA 1, yet, even with the above it returns a 0.

I will admit that I wrote in the previous post that both your and Yjoshi's formulas worked, but had only tried Yoshi's, which did work, and return a value of 1.

I must be missing something. Thank you again for your help and patience. I like your idea better of finding "app" anywhere in the word, as opposed to only the first letters to the LEFT, so I'm hoping I can get your formula to work.

EDR

Have you tried this formula? Just wondering, because even with lower case, I'm getting a return value of zero.

In cell C50, it's written in upper case: PASTA 1

This formulas returns a value of zero:

=SUM(IF(ISNUMBER(FIND("pas",LOWER(C8:C91),1)),1,0))

This one also returns zero:

=SUMPRODUCT(IF(ISNUMBER(FIND("pa",LOWER(C8:C91),1)),1,0),IF(ISNUMBER(FIND("1",C8:C91,1)),1,0))

Tks. EDR

Thank you

Quote from yjoshi

Hi,

"=SUMPRODUCT(ISNUMBER(FIND(B2,LOWER(A2:A5),1))*ISNUMBER(FIND(C2,A2:A5,1)))"

What do you think???

This worked perfectly, as soon as I typed it in. Thank you.

Well, actually, I had to modify it to work in my sheet, but no problems whatsoever:

=SUMPRODUCT(ISNUMBER(FIND("pa",LOWER(C8:C91),1))*ISNUMBER(FIND(1,C8:C91,1)))

The above returned a value of 1 for me, as there is a Pasta 1 in cell C50.

Weasel, thanks for the spreadsheet, and for your time. I really do appreciate it. And, while it works in your sheet if "pa" and "1" are in different cells (B2 and C2) - type "pa" with quotes into your formula in your spreadsheet, and the number drops to 1 on my end, even though there are two PASTA 1's. Seriously, there's something quirky going on, and I don't know what it is? It's probably me, but for some reason when I type in Yjoshi's formula it works corectly immediately, and when I type yours in, it won't work, even if I mess with it a bit?

This is what I typed into your spreadsheet and it changed to 1:

=SUMPRODUCT(IF(ISNUMBER(FIND("pa",LOWER(A2:A5),1)),1,0),IF(ISNUMBER(FIND(1,A2:A5,1)),1,0))

Notice, I stopped looking in B2, but in the A array for "pa", and the value dropped to one?

--------------------------------------------------------------------------

LAST REQUEST

I have only one more request, and I will leave you two very kind people alone. I want to add one more variable to the equation, which is a bit beyond my Excel knowledge.

Using Yjoshi's formula:

=SUMPRODUCT(ISNUMBER(FIND("pa",LOWER(C8:C91),1))*ISNUMBER(FIND(1,C8:C91,1)))

... how can I check the cell immediately below any cell that has a true value via the above for an "A.M." value?

For example, let's say using Yjoshi's formula that it finds cell C50 has a "PASTA 1". Since that is true, only THEN do I want it to look for "A.M." and only in cell C51. If all are true, THEN I want to multiply the number of times it is true in the array.

Example #2: If Pasta1 is found in cell E25, then I need to check E26 for "A.M." If all are true, then I add a value of 1.

That would be it, and I will be out of your hair. Thank you all so much! I do appreciate your time.

EDR

Hi Derk:

Thank you. Both formulas, however, are giving me a value of zero. Yjoshi's second formula using SEARCH didn't work for me. Only one of his formulas is working perfectly. I'm computer savy enough to check the formulas and corresponding cells, and to experiment with "" and no quotes. I cannot get any of the formulas to work except:

=SUMPRODUCT(ISNUMBER(FIND("pa",LOWER(C8:C91),1))*ISNUMBER(FIND(1,C8:C91,1)))

I do, however, notice that the formulas work in Weasel's spreadsheet, but the difference there again is that "pa", and 1 are in DIFFERENT cells, and the formula is actually calling B2 and C2, not words in quotes, such as "pa" and "1". That seems to screw up the value, when I modify the formula to look for the same values in ONE cell, not two different cells?

In any case ... Derk, I think your new formula wouldn't be exactly correct anyway because it would look for A.M. throughout the entire array, and there may be an A.M. 20 cells lower than the cell that contains "pa" and "1". Thus, with your formula the output would be true, even though it is not, no?

Again, what I need is to output a value of 1 only IF:

Any cell contains "pa" and "1", and the cell directly below it contains "A.M." anywhere in the cell. If all three of those variables are true, then I need a value of 1 as the output.

C50 = Pasta 1
C51 = 8:00 AM

Value = 1

C90 = Pasta 1
C95 = 10:00 A.M.

Value = 0 (false)

Thank you again! EDR

Quote from thomach

Try:

=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91))*ISNUMBER(SEARCH("A.M.",C9:C92)))

This should work IF the "A.M. is actually typed into the cells as text. If you have a time in the cell and simply have the formatting set to display the time with an A.M. -- P.M. format, then this will not work because the cell value actually does not contain the "A.M."

Thomach:

You are correct. Darnet! OK, so how can I overcome this hurdle? The cell below is formatted to show "A.M", as part of a time input. This I cannot change. I need to know whether someone is scheduled for a particular station (restaurants obviously) for a particular morning or evening shift. This way I can separate the two.

My goal here is to be able to alert the person creating the schedule at times when they've forgotten to schedule various stations, for morning and evening shifts.

Thus, if Value=0, the formatted cell will change to a red color alerting the user that they forgot to schedule a particular station for a particular shift.

However, if Value>0, then at least one person was scheduled, and the cell will not change colors to alert the user. I have all of this figured out and working perfectly, I just need the right formula now to be able to do this. Hmmm? Since I cannot change the entire format of the program, I'm stuck with 8:00 A.M. or 4:00 P.M., etc. Could I do something that says, "AND X<12" for an A.M. shift, and X>13:00 for a P.M. shift? Something like that?

You guys rock! Thanks again for all of your time and assistance. I'm sure we'll get it sooner or later.

EDR