Search a string with a series of arguments and return their position

[Costasenos]

So, on sheet1, column A, I have strings such as
MIKE 65 UUUU 3434
BEARINGUUUU OK DDFDF8
ENOUGHAAA999
ZZZZZZZZZZZZZZZZZZZZZZ
...

On sheet2, column C, I have the following 3 arguments (I actually more than 3 (9), but for the sake of the example...)
AAA
OK
UUUU

Most of the times, each string of sheet1colA will contain only one of the arguments listed in sheet2colC.

What I want:
For every string of sheet1colA, list on sheet1colB the text that comes after the argument found therein.

I would like Column B of sheet1 to look like this:
3434
OK DDFDF8
999
(not applicable - error value)
...

Thanks for the one who'll find this!

PS: following array formula already allows me to find whether the string contains any of the arguments:
=IF(SUM(NOT(ISERROR(FIND(Sheet2!$C$1:$C$3;A1)))*1)>0;TRUE;FALSE)

 

[Costasenos]

So... I was able to find the trick: use the following array formula:
+{MID(A2;VLOOKUP(MATCH(TRUE;ISNUMBER(1*FIND(SHEET2!$C$2:$C$12;C2;1));0);SHEET2!$C$2:$C$12;3;FALSE)+FIND(VLOOKUP(MATCH(TRUE;ISNUMBER(1*FIND(SHEET2!$C$2:$C$12;A2;1));0);SHEET2!$C$2:$C$12;2;FALSE);A2;1);99)}

I'm now looking for a way to integrate, in this formula, the selection of only the highest value of the string found in column A of sheet1.

That is, if string of sheet1colA would contain
RRRRCRITERIA1ZZZZZZZZZZZCRITERIA2PPPPPPPPPPPPPPPP
1 5 25

and the criteria on sheet1colC would be
CRITERIA1
CRITERIA2

I would want the formula to return "PPPPPPPPPPPPPPPP", that is "the furthest part of the string", in this case, as of char 25.

At this moment, my formula returns all characters following char 5, since it's the first that comes in the criteria list sorted by alphabetical order.

Sorting the criteria list non-alphabetically would not solve the problem for strings having CRITERIA2 preceding CRITERIA1.

Fancy hein? ;D

Thx for any support, and hope the input above already helps some.

Cheers

 

[Aladin Akyurek]

So, on sheet1, column A, I have strings such as
MIKE 65 UUUU 3434
BEARINGUUUU OK DDFDF8
ENOUGHAAA999
ZZZZZZZZZZZZZZZZZZZZZZ
...

On sheet2, column C, I have the following 3 arguments (I actually more than 3 (9), but for the sake of the example...)
AAA
OK
UUUU

Most of the times, each string of sheet1colA will contain only one of the arguments listed in sheet2colC.

What I want:
For every string of sheet1colA, list on sheet1colB the text that comes after the argument found therein.

I would like Column B of sheet1 to look like this:
3434
OK DDFDF8
999
(not applicable - error value)
...

Thanks for the one who'll find this!

PS: following array formula already allows me to find whether the string contains any of the arguments:
=IF(SUM(NOT(ISERROR(FIND(Sheet2!$C$1:$C$3;A1)))*1)>0;TRUE;FALSE)

Name List the range of the search strings on Sheet2.

Also, define BigNum as referring to:

=9.99999999999999E+307

Sheet1

B2, just enter and copy down:

=TRIM(REPLACE(A2,1,LOOKUP(BigNum,SEARCH(List,A2))+
    LEN(LOOKUP(BigNum,SEARCH(List,A2),List))-1,""))

 

[Costasenos]

Bedankt!
Works very well and is less complicated than my formula.
Applying it twice also solves my second problem.
Thanks!
Cheers.
Costas

 

[Aladin Akyurek]

Bedankt!
Works very well and is less complicated than my formula.

Graag gedaan.

Applying it twice also solves my second problem.
...

You mean #N/A problem? If so, the following is less costly:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",
   TRIM(REPLACE(A2,1,LOOKUP(9.99999999999999E+307,
    SEARCH(List,A2))+LEN(LOOKUP(9.99999999999999E+307,
     SEARCH(List,A2),List))-1,""))))

If you are on Excel 2007 or later...

=IFERROR(TRIM(REPLACE(A2,1,LOOKUP(9.99999999999999E+307,
    SEARCH(List,A2))+LEN(LOOKUP(9.99999999999999E+307,
     SEARCH(List,A2),List))-1,"")),"")

 

[Costasenos]

Thx.

You mean #N/A problem? If so, the following is less costly:

No, I was referring to the fact that I would have to apply the formula two times (two columns) in case there would be two criteria in the same string of colA.
See example below:

I'm now looking for a way to integrate, in this formula, the selection  of only the highest value of the string found in column A of sheet1.

That is, if string of sheet1colA would contain
RRRRCRITERIA1ZZZZZZZZZZZCRITERIA2PPPPPPPPPPPPPPPP
1     5                                25

and the criteria on sheet1colC would be
CRITERIA1
CRITERIA2

I would want the formula to return "PPPPPPPPPPPPPPPP", that is "the furthest part of the string", in this case, as of char 25.

At this moment, my formula returns all characters following char 5,  since it's the first that comes in the criteria list sorted by  alphabetical order.

Sorting the criteria list non-alphabetically would not solve the problem for strings having CRITERIA2 preceding CRITERIA1.

It's not a biggie, but would be convenient to have on only one column :D
Cheers