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

Costasenos

New Member
Joined
Mar 3, 2011
Messages
5
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
Joined
Mar 3, 2011
Messages
5
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
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,""))
 
Upvote 0

Costasenos

New Member
Joined
Mar 3, 2011
Messages
5
Bedankt!
Works very well and is less complicated than my formula.
Applying it twice also solves my second problem.
Thanks!
Cheers.
Costas
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,"")),"")
 
Upvote 0

Costasenos

New Member
Joined
Mar 3, 2011
Messages
5
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:
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top