 # Finding consecutive letters in a column

• Quote from Derk

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?

Hi Derk:

I'm running Excel 2003. I'm not sure why only one of Yjoshi's formulas work. The one with SEARCH returns a value of zero? I've triple checked it. I do not know why?

I'm going to try it again right now, using:

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

Although there is clearly a "PASTA 1" in cell C50, the above formula returns a value of zero.

Again, I do not know why.

However, using this formula, a value of 1 is returned (which is correct):

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

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

CELLS DIRECTLY BELOW

The cells right below are formatted as:

Custom: h:mm AM/PM

Thus, when someone types in 8:00, it auto changes to 8:00 AM.

I want to avoid having to type in military time because the entire format and all related formula cells would have to be changed, which is a much larger project than I want to get into.

• Here is one way. It involves adding a column since I haven't gotten it to work in a single vector formula. I used column D for my formulas.. Starting in D8 enter:

=IF(ISNUMBER(C8),MOD(C8,1),"")

Drag this down through cell D92.

Then in the cell where you want your total "hits" enter:

Code
``=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91))*(D9:D92<0.5))``

Alternatively, in cell D8 enter:

Code
``=IF(AND(ISNUMBER(C9),NOT(ISERROR(MOD(C9,1))),ISNUMBER(SEARCH("pa*1",C8))),MOD(C9,1)<0.5,"")``

and drag this down column D. It will put a TRUE in the rows where there is an A.M. match, a FALSE in rows with a P.M. match, and nothing in rows with non matches.

• =SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))) doesn't work because there is no arithmetic operation to turn the true.false to numbers. Try =SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))*1) which will work.

• Thomach:

Thank you so much! Unfortunately, I'm not at liberty to add an additional column, as there are already multiple columns being utilized (multiple days and stations). It would take some extensive work.

For now, I think I'll settle for:

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

The above seems to working, the "A.M." issue aside. If anyone knows of a way to incorporate the A.M. into a single vector with the above formula, I'm all ears. If not, I'll simply settle for the above, and leave it as is.

Since the A.M. and P.M. categories are split in all sheets, but one, I'll find some other work-around, as I only need the "A.M." check for one sheet.

Again though, if anyone knows of a way to do this in a single cell formula I'm open to it.

Thanks everyone for all of your expertise, time and assistance. Everytime I have visited this site, the assistance has been very timely, friendly and first rate! Ozgrid rocks! EDR

• Quote from Derk

=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))) doesn't work because there is no arithmetic operation to turn the true.false to numbers. Try =SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1))*1) which will work.

Derk: You are correct. I tried it, and this does work! Thank you very much!

Now, if anyone has an idea for a single column (single cell) formula to incorporate the above AND checking for "A.M." or "P.M." in the cell directly below (coded in time format, not as actual text), I'd be in fat city.

Tks. EDR

• You may not have seen my earlier post because you posted shortly threreafter. It said

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))

• Yeah baby!

Quote from Derk

You may not have seen my earlier post because you posted shortly threreafter. It said

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))

Hi Derk!

That's the ticket. The CTRL-SHIFT-ENTER is obviously necessary. I may have been missing that earlier, but in any case, problem solved. Thank you so much!

I'm assuming these formulas will be backward comptible to older versions of Excel? Altough I am coding in Excel 2003, this program will be used on all version from '97 forward. Any concerns?

EDR

• Hi,

I am using Excel 2000, so no problem for this as well.

As this quote has gone in a deadly loop.... it is easy to miss out on the final puroose of the formula for a person reading later.

I am attaching a sheet which explains step by step what is done, and tries to tally the result with the desired one.

Hope this helps.

## Files

Thanks: ~Yogendra

• Thank you

Derk & Yjoshi:

Thank you. EDR

## Participate now!

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