Partial Join or Find & Replace with a partial string

[snargaluff]

Hi all,

I've been trying to search for an answer on this board for my issue, but ones that I felt were close to addressing did not fix.

Basically, I'm trying to find a text within one cell, locate it in another cell, and if it is included, to change or append the entire identifier to the original text. Otherwise, keep the original identifier.

Table 1:

REF 1

ATX-AC8

ABX-TZM

ASX-ADY

NZX:ATM

BBC:TYD

RKD:PTDS

Table 2:

REF 2

BRCNF ATX-AC8

APROPT ABX-TZM NZX:ATM

ADBCF ASX:ABC

ADMRF ASX-ADY

RKD:PTDS

What I would like my end result to be:

REF 1	NEW_REF
ATX-AC8	BRCNF ATX-AC8
ABX-TZM	APROPT ABX-TZM NZX:ATM
ASX-ADY	ADMRF ASX-ADY
NZX:ATM	APROPT ABX-TZM NZX:ATM
BBC:TYD	BBC:TYD
RKD:PTDS	RKD:PTDS

Hope that makes sense, appreciate the help

[Qiu]

@snargaluff 
Maybe something like this?
I am using append tool so hope your data is not so huge.

[atcodedog05 · Accepted solution]

Hi @snargaluff 

Here is my take on it

Hope this helps : )

[snargaluff]

Thank you! I will try this

[snargaluff]

Wow this is brilliant! I do have a large dataset, but I will try this out anyways!

[atcodedog05]

Happy to help : ) @snargaluff 

Cheers and have a nice day!