Formula to find text pattern across multiple cells in a row.

[DocAElstein]

Hello whiteheadbell,
Welcome to excelfox, I didn’t find your post long, - its better to give a full explanation as you did. :-)

Formulas are not my strong point.
I think I can see a way to do this. It may not be the best way.
The formula ideas that I have result in a long formula. This is usually an indication that it is a bad way to do it.

Anyway… if you simply put all the text from the cells together ( called concatenating ) , then you can use Find on that concatenated string . If I understand correctly, Find returns the “position along counting from the left” of the start character of what it is looking for in the character string that it is given to look in. That seems to be coincidentally the number that you are looking for.
( I guess if you actually wanted to get what is in the cell in that first row then you would use the Index function with that found position along, or VLookUp somehow )

( The formula is not very nice to write in manually, but I did not have to. It is quite easy using VBA coding to make a formula like that, so that is what I did. )

I used the range BB4 – BD5 to make the formulas

See if this is a help

_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )

Row\Col	BB	BC	BD
4	yyyyynynyyynnynnnynnnnynynnnnnnnynnnnynnnnnnnnnnnn	14	14
5	yyyyyyyyyyyyynynyyyynyynnyynnnnnnnnnnnnnnyynynnnnn	27	27

Worksheet: List 1

_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )

Row\Col	BB	BC	BD
4	=B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4	=FIND("ynnn",BB4)	=FIND("ynnn",B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4)
5	=B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5	=FIND("ynnn",BB5)	=FIND("ynnn",B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5)

Worksheet: List 1

Alan

P.s. For large amounts of data, a VBA coding solution is usually regarded as better. Lots of long formulas can make a workbook unnecessarily large and possibly slow

P.s. 2 I think there is a CONCATENATE function, and for higher versions of Excel the CONCAT function, which can simplify the formula a bit