Extract a substring with find and mid

[AlexRoberts]

I have searched many forums but am having trouble finding an example of exactly what I am trying to achieve.

There are many examples to extract a substring with find and mid for a single occurrence of the string, but what if there were multiple occurrences.

For example the string below is in cell A1 and want to extract the substrings ALEX01, ALEX02 and ALEX03 into cells A2, A3 and A4.

ALEX01FRED01ALEX02FRED02ALEX03FRED03.

Thank you.

[NeedForExcel]

Here!

Put this formula in Cell A2 and Drag Down

=MID($A$1,(ROW(A1)*6)-5,6)

Deep

[NeedForExcel]

I have assumed in the Above formula that the length of each entry would be 6 Characters only.

Deep

[AlexRoberts]

Deep,

Sorry, a small mistake in my original post, the ouput to be in B1, C1, D1.

Also the formula you posted extracts every 6 text strings into the cell, I want only to extract ALEX and not FRED.

Can you still help me.

[NeedForExcel]

Do you want to Extract Alex along with the 2 Numbers ahead of it or just Alex??

[NeedForExcel]

Here!

Put in in Cell B1 and Drag Across -

=MID($A$1,(COLUMN(A1)*12)+1-12,6)

Assumed that you need Alex with 01 & Alex & Fred would appear alternately.

If not, then please specify further.

Deep

[kvsrinivasamurthy]

If data is in column A, put this formula(ARRAY FORMULA) in B1 and drag across and downwords .Pl see attached file.

=IFERROR(MID($A1,SMALL(IF(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1)-6)),4)="ALEX",ROW(INDIRECT("A1:A"&LEN($A1)-6)),""),COLUMN(A1)),6),"")

[AlexRoberts]

kvsrinivasamurthy.

Thank you. Exactly what I wanted.