Nesting

[Weldon Riker]

Excel Gurus

To the formula below, I need to add another "FIND". . .I need the formula to find "RC" as well. . .


=MID(G2,IFERROR(IFERROR(FIND("LI",G2),FIND("DP",G2)),LEN(G2)-6),7)

 

[Luke M]

Could change to this setup:
=MID(G2,IFERROR(MIN(FIND({"LI","DP","RC"},G2)),LEN(G2)-6),7)

Now you can easily add more strings to search for if needed.

 

[Weldon Riker]

That looked elegant! but the LEN part of the formula would have to change as it is now returning the wrong value. The formula has to find "LI" and return LI 0000, or DP 0017, or RC 0037 so on. . .

 

[Weldon Riker]

I uploaded a file for more ease of understanding. . . .I hope

 

[John Jairo V]

Hi @Weldon Riker (and @Luke M)

An option could be:
=MID(A1,IFERROR(AGGREGATE(15,6,FIND({"LI";"DP";"RC"},A1),1),LEN(A1)-6),7)

Blessings!

 

[Weldon Riker]

Hi @Weldon Riker (and @Luke M)

An option could be:
=MID(A1,IFERROR(AGGREGATE(15,6,FIND({"LI";"DP";"RC"},A1),1),LEN(A1)-6),7)

Blessings!

That worked beautifully!! Thank you so very much. . .

You guys are the best! No question about it. . .

 

[Luke M]

Ah, I see. If all your data follows a similar layout, will this work?
=MID(A1,IF(OR(ISNUMBER(FIND({"LI","DP","RC"},A1))),12,LEN(A1)-6),7)

Edit: Nice John! Forgot that AGGREGATE could handle the issue.