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

#### Costasenos

##### New Member
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)

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Costasenos

##### New Member
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

##### MrExcel MVP
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:
Rich (BB code):
``````=TRIM(REPLACE(A2,1,LOOKUP(BigNum,SEARCH(List,A2))+
LEN(LOOKUP(BigNum,SEARCH(List,A2),List))-1,""))``````

#### Costasenos

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

##### MrExcel MVP
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:
Code:
``````=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...
Code:
``````=IFERROR(TRIM(REPLACE(A2,1,LOOKUP(9.99999999999999E+307,
SEARCH(List,A2))+LEN(LOOKUP(9.99999999999999E+307,
SEARCH(List,A2),List))-1,"")),"")``````

#### Costasenos

##### New Member
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:
Code:
``````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

Last edited:

Replies
10
Views
715
Replies
18
Views
1K
Replies
1
Views
602
Replies
0
Views
501
Replies
1
Views
454

1,191,274
Messages
5,985,698
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back