I have 2 MID function formulas that I use to extract data on a specific cell string on an excel spreadsheet which work perfectly fine for me when used separately in different columns, but I would like to use these formulas together with an "OR" logic on the same column since my data could be either or, and will never have both conditions on a single cell, please assist
=MID(C4, FIND("ML", C4), LEN(C4) - FIND("ML", C4) + 1)
=MID(C4, FIND("PL", C4), LEN(C4) - FIND("PL", C4) + 1)
I have exhausted all the options that I could think of, but nothing seems to work
Sample data below:
xyz drt ML0000123456
hrz sd fri PL0000987456
asdfghg ML5236987412
lhkghibkjn PL1236540001
>Solution :
Try one of the followings:
=IFERROR(REPLACE(A1,1,SUM(IFERROR(FIND({"ML","PL"},A1),0))-1,""),"")
Or,
=IFNA(TEXTAFTER(A1,TEXTBEFORE(A1,{"ML","PL"})),"")
Or, a formula like this would be better:
=LET(
a, TEXTSPLIT(A1," "),
FILTER(a,(OR(LEFT(a,2)={"ML";"PL"}))*(LEN(a)=12)*(ISERR(--a)),""))