# 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

• Hi,

You can do it in two ways. Simple formula and array formula.

If you want a simple formula, : enter following in any cell other than A1 to A9
=SUMPRODUCT((LEFT(UPPER(A1:A9),3)="APP")*1)

What it does is....
1. Takes values in A1 to A9 and makes it CAPITALIZED
2. Takes first 3 chars and compates with APPS
3. if this is true, it generates an array of TRUE & FALSE
4. TRUE = 1 and FALSE = 0
5. when you multiply it by one, it creates series of 1s and 0s
6. when you do a sumproduct, it totals all 1s and gives you the result.

IF you want to use Array Formula for this : use following
{=SUM((LEFT(UPPER(A1:A9),3)="APP")*1)}

Note that, you have to enter =SUM((LEFT(UPPER(A1:A9),3)="APP")*1) and press
"Ctrl + Shift +Enter" to get the { } s and not enter manually.

You can see following links for getting more info on WHAT CAN BE ACHIEVED WITH ARRAY FORMULAE :: enjoy
http://www.emailoffice.com/excel/arrays-bobumlas.html

Also see array formulas link on OZGRID.
http://www.ozgrid.com/Excel/arrays.htm

Thanks: ~Yogendra

• The following formula should be array entered (use Shift-Ctrl-Enter):

=SUM(IF(ISNUMBER(FIND("app",LOWER(A1:A4),1)),1,0))

This example works on the range A1:A4 and will return the number of cells that contain the test string "app".

Hope that helps
Weasel

• 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

• For this I have switched to the SUMPRODUCT formula used by yjoshi. It also must be array entered:

=SUMPRODUCT(IF(ISNUMBER(FIND("app",LOWER(A1:A4),1)),1,0),IF(ISNUMBER(FIND("1",A1:A4,1)),1,0))

Note that I persevere with the FIND method as this will work not only with Appertiser 1, Appetizer 1, app 1 ...... but also with Prawn Appetiser 1, Small appetiser 1, appetiser 1 Prawn ....... etc.
Using the LEFT method results are restricted to entries that start with app.

Regards
Weasel

• 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

• Note that the formula converts all entries in the array to lower. As such you must use lower case as your find criteria.

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

Regards
Weasel

• 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

• Hi,

I think the use of If is not really required,

What you can do is.... multiple the two series of true & false and you will get the numbers...

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

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

This will certainly reduce the code, and also use internal features of excel (i.e. TRUE = 1 & False = 0) and their multipliaction...

I tried it in your sheet and it worked well.

What do you think???

Thanks: ~Yogendra

• Whats more .... found another shorter way to do this.....

"=SUM((ISNUMBER(SEARCH(B2&"*"&C2,LOWER(A2:A5),1)))*1)"

Using search function using * operator

It tries to search "pa*1" in the range....

You never know what you can find in excel

See attachment

## Files

Thanks: ~Yogendra

• 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

• Try
=SUMPRODUCT(ISNUMBER(FIND("pa",LOWER(C8:C91),1))*ISNUMBER(FIND(1,C8:C91,1))*(C9:C92="A.M."))

• Going back to the earlier replies, Search seems a better option than Find. See how
=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))*(C9:C92="A.M."))
works for you.

• 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

• 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."

• 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

• Leaving aside the time format for the moment, my earlier formula and Tom's modification work because of the offset by 1 of the search range C9:C92 in the last expression. Because the two expressions (pa*1 and A.M.) are multipluied together they will return true only if the A.M. is in the next line after the pa*1 line.

Now, why does search not work for you? What version of Excel are you running?

As to time, theer is a way around that if we can assume the only cells with just numbers in the range C9:C92 are formatted as dates. Is that the case?

• Given the assumption all numbers are times (and Search works for you), the following array function (enter with Ctrl+Shift+Enter) should work for AM times.
=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))*IF(ISNUMBER(C9:C92),MOD(C9:C92,1)<0.5,FALSE))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!