CODEWITHSUNDEEP
stringexceltextmatching
david taylor
david taylor

Reputation: 1

excel search for a string in a table and return string if true

Column A contains a varying text - around 1000 entries but the same text will appear in different cells.

In a second separate column (Column G) = a separate table, each cell contains one of a number of set text strings (each can be 2 to 5 characters long). Around 20 in all.

I want to search the start of each cell in column A for each of text strings in column G and if one is present (say G3's contents) I want G3's contents to be shown in column B in the same row as the matched cell from column A.

The matter is complicated by the text to be matched containing both ASC and SC and a further single charcater match being on * (although I can exclude * if this gives a simpler result).

Examples

Column A contains
ASC1 HHHH.........
ASC2 IIII.......
ASC2 AAAA.........
SC2 JJJJ....
TRIAL HOLE........
TRIALHOLE......
OP1 MMMM ...
ASC1COMPLEX
KAN1
PS BAN
*TTC Jiokl jj

Column G
ASC1
ASC2
*
OP1
PS
SC2
TRIAL
VERIFY

So column B should show
ASC1
ASC2
ASC2
SC2
TRIAL
TRIAL
OP1
ASC1
0 (or some other error/omit value)
PS
*

(How) can this be done? I have tried to adapt various solutions from the internet and failed.

Many thanks DaveT

Upvotes: 0

Views: 27498

Answers (2)

barry houdini
barry houdini

Reputation: 46331

Assuming G2:G20 contains the search strings - if you can list them in order of length (shortest first) so given your sample values the list would start with

* PS OP1 SC2 ASC1 ASC2 TRIAL VERIFY

then use this formula in B2 copied down

=LOOKUP(2^15,FIND(G$2:G$20,A2),G$2:G$20)

G2:G20 should be fully populated or adjust the range used - The matching is case-sensitive and #N/A is returned if there is no match

If you want to allow blanks in G2:G20 use this version

=LOOKUP(2^15,FIND(G$2:G$20,A2)/(G$2:G$20<>""),G$2:G$20)

Upvotes: 3

rwking
rwking

Reputation: 1032

This works if you place an asterisk at the end of each string in Column G. The asterisk is a wildcard which allows the MATCH() function to search based on the string plus any number of characters that may follow it.

Paste this into cell B1.

=IF(ISNA(MATCH($G$1,A1,FALSE)),"",LEFT($G$1,SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$2,A1,FALSE)),"",LEFT($G$2,SEARCH("*",$G$2,1)))&IF(ISNA(MATCH($G$3,A1,FALSE)),"",LEFT($G$3,SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$4,A1,FALSE)),"",LEFT($G$4, SEARCH("*",$G$1,1)))&IF(ISNA(MATCH($G$5,A1,FALSE)),"",LEFT($G$5, SEARCH("*", $G$5,1)))

You'll have to repeat the "&IF(ISNA(MATCH($G$5,A1, FALSE)), "", LEFT($G$5,SEARCH("*",$G$5,1)))" portion for each cell in Column G where you have a string you want to search.

Then copy the formula in B1 all the way down by double-clicking on the cross-hair in the bottom right hand corner of the cell.

This will not place anything in the cell if there is not match. It's also not case sensitive, but I'll be the first to admit that if your data is in all caps as it appears, then go for the answer above. It's simpler.

Upvotes: 0

Related Questions