Search code examples
excelexcel-formula

Excel MID function

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:

    enter image description here

    =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)),""))