Extract the strings between the most right "ABC" and the tailing "CBA"

[Tom2]

Hello Experts,

I'm trying to use MID and FIND to extract strings between the most right "ABC" and tailing "CBA" but failed. Sample file attached. Each string may have more than one "ABC" string. The goal is to find the most right one and then extract the strings between it and the tailing "CBA". I tried to use MID and FIND functions but failed.

Any suggestion is greatly appreciated.

 

[ETAF]

this is case sensitive as using ``find"

=TEXTBEFORE(MID(B9,FIND("@@@",SUBSTITUTE(B9,"ABC","@@@",(LEN(B9)-LEN(SUBSTITUTE(B9,"ABC","")))/LEN("ABC")))+3,100),"CBA")

I suspect a TEXTAFTER will

will they always be uppecase ?

 

[djc]

With Excel for MS365 or Excel for the web, a simple TEXTBEFORE-TEXTAFTER combination will do the trick:

=TEXTBEFORE(TEXTAFTER(B9:B13, "ABC", -1), "CBA", -1)

For older versions of Excel, one possibility could be:

=REPLACE(LEFT(B9, LEN(B9)-LEN("CBA")), 1, SEARCH("|", SUBSTITUTE(B9, "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(B9, "ABC", )))/LEN("ABC")))+LEN("ABC")-1, )

//OR

=REPLACE(LEFT(B9, LEN(B9)-3), 1, SEARCH("|", SUBSTITUTE(B9, "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(B9, "ABC", )))/3))+2, )

Regarding Case #2, the TRIM function can be used to remove the leading and/or trailing spaces from the results. See attached...

 

[Tom2]

this is case sensitive as using ``find"

=TEXTBEFORE(MID(B9,FIND("@@@",SUBSTITUTE(B9,"ABC","@@@",(LEN(B9)-LEN(SUBSTITUTE(B9,"ABC","")))/LEN("ABC")))+3,100),"CBA")

I suspect a TEXTAFTER will

will they always be uppecase ?

Great question. If not always be uppercase, is there a more scalable/general formula to extract the strings? Thanks.

 

[Tom2]

With Excel for MS365 or Excel for the web, a simple TEXTBEFORE-TEXTAFTER combination will do the trick:

=TEXTBEFORE(TEXTAFTER(B9:B13, "ABC", -1), "CBA", -1)

For older versions of Excel, one possibility could be:

=REPLACE(LEFT(B9, LEN(B9)-LEN("CBA")), 1, SEARCH("|", SUBSTITUTE(B9, "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(B9, "ABC", )))/LEN("ABC")))+LEN("ABC")-1, )

//OR

=REPLACE(LEFT(B9, LEN(B9)-3), 1, SEARCH("|", SUBSTITUTE(B9, "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(B9, "ABC", )))/3))+2, )

Regarding Case #2, the TRIM function can be used to remove the leading and/or trailing spaces from the results. See attached...

Thanks djc!!!! It works like a charm.

​

 

[ETAF]

the text before and textafter - is also case sensitive, SO in example only uppercase

=TEXTBEFORE(TEXTAFTER(B9:B30, {"abc","ABC"}, -1), {"cba","CBA"}, -1)

BUT will not do a mixture of upper and lowercase

 

[Tom2]

the text before and textafter - is also case sensitive, SO in example only uppercase

=TEXTBEFORE(TEXTAFTER(B9:B30, {"abc","ABC"}, -1), {"cba","CBA"}, -1)

BUT will not do a mixture of upper and lowercase

much appreciated!!!

 

[djc]

Thanks djc!!!! It works like a charm.

​

You're welcome! To follow-up regarding case...

With the TEXTBEFORE-TEXTAFTER method, you can set the optional [match_mode] argument to 1 (Case-insensitive match):

=TEXTBEFORE(TEXTAFTER(B9:B13, "abc", -1, 1), "cba", -1, 1)

And with the legacy formula, just add the UPPER function to the text argument of SUBSTITUTE to ignore case. For example:

=REPLACE(LEFT(B9, LEN(B9)-3), 1, SEARCH("|", SUBSTITUTE(UPPER(B9), "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(UPPER(B9), "ABC", )))/3))+2, )

Cheers!

 

[Tom2]

You're welcome! To follow-up regarding case...

With the TEXTBEFORE-TEXTAFTER method, you can set the optional [match_mode] argument to 1 (Case-insensitive match):

=TEXTBEFORE(TEXTAFTER(B9:B13, "abc", -1, 1), "cba", -1, 1)

And with the legacy formula, just add the UPPER function to the text argument of SUBSTITUTE to ignore case. For example:

=REPLACE(LEFT(B9, LEN(B9)-3), 1, SEARCH("|", SUBSTITUTE(UPPER(B9), "ABC", "|", (LEN(B9)-LEN(SUBSTITUTE(UPPER(B9), "ABC", )))/3))+2, )

Cheers!

Wow, it's really great idea!!! Thanks a lot!

 

[p45cal]

Just for fun:

=TRIM(TAKE(TEXTSPLIT(B9,{"ABC","CBA"},,TRUE,1),,-1))

 

[bosco_yip]

Try to use MID and FIND functions as in:

1] Case #1: no space between strings
In F9, formula copied down:
=MID(LEFT(B9,LEN(B9)-3),1-LOOKUP(1,-FIND("C",LEFT(B9,LEN(B9)-3),ROW($1:$99))),99)

2] Case #2: with space between strings
In F22, formula copied down:
=TRIM(MID(LEFT(B22,LEN(B22)-3),1-LOOKUP(1,-FIND("C",LEFT(B22,LEN(B22)-3),ROW($1:$99))),99))