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.

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

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

Thank you

Derk & Yjoshi:

Thank you. EDR