FInding a string within a string ?

[JumboCactuar]

Hi
this is probably basic stuff but I've never done this before

I have a code table:

RED	Red
BLA	Black
BLU	Blue
YEL	Yellow
PIN	Pink

etc....

and I have long strings like
002147YELXR3
00732933BLA22
etc...

trying to output in next column to string the name of the code like above would be
Yellow
Black

if the color codes we're in fixed position I could do this easily but the codes can be in ANY part of the string.


Best way to do this ?

thanks

 

[CA_Punit]

Try this

Book1
	C	D	E	F
2
3		RED	RED
4		BLA	BLACK
5		BLU	BLUE
6		YEL	YELLOW
7		PIN	PINK
8
9
10		002147YELXR3		YELLOW
11
Sheet4

Cell Formulas
Range	Formula
F10	F10	=LOOKUP(999999,IF({1,0},SEARCH(D3:D7,D10)^0*(ROW($D$3:$D$7)-ROW($D$3)+1),$E$3:$E$7))

 

[alansidman]

Using Power Query called Get and Transform in 2016 and later versions.
1. Bring each table into PQ Editor.
2. With Table1 listing the codes the following Mcode is applied to parse out the 3 letter color code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.2.1", "Column1.2.2"}),
    #"Split Column by Position" = Table.SplitColumn(#"Split Column by Character Transition1", "Column1.2.1", Splitter.SplitTextByPositions({0, 3}, false), {"Column1.2.1.1", "Column1.2.1.2"})
in
    #"Split Column by Position"

3. The two tables are now joined (merged in PQ terminology) with a left inner join. The Mcode for that follows

let
    Source = Table.NestedJoin(Table1, {"Column1.2.1.1"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column2"}, {"Table2.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Column1.1", "Column1.2.1.1", "Column1.2.1.2", "Column1.2.2"})
in
    #"Removed Columns"

Solution shown below

Book8
	A	B
1	Column1 - Copy	Table2.Column2
2	00732933BLA22	Black
3	002147YELXR3	Yellow
Sheet3

 

[JumboCactuar]

@CA_Punit
i tried the formula but with my large dataset and it just outputs zero

=LOOKUP(999999,IF({1,0},SEARCH(B2:B200000,I2)^0*(ROW($B$2:$B$200000)-ROW($B$2)+1),$C$2:$C$200000))

Lookup value is I2
Code column is B
Name column is C

 

[CA_Punit]

Try with Control+Shift+Enter

 

[JumboCactuar]

Try with Control+Shift+Enter

still 0 output

 

[JumboCactuar]

@alansidman

unsure where to put the 2nd PQ
i tried it on table 2 and gives cyclic expression error

would prefer formula solution (or VBA) if its possible

 

[CA_Punit]

Do you have blank cell in

B2:B200000

 

[CA_Punit]

If Yes just replace the search

(SEARCH(B2:B200000,I2))

with this

SEARCH(IF(($B$2:$B$20000<>""),($B$2:$B$200000),REPT("Z",900)),I2)

 

[alansidman]

Here is the file so that you can see the whole story.

Book10.xlsx | Powered by Box

app.box.com