Compare two Columns for string to extract matched value and put it in 3rd column

[ahsansalik]

I have data in two columns i want to take value from Column 1 and then compare with Column 2 and extract the string plus 2 additional digits next to it and put them in column 3. the data in column 2 is manually entered so the placement of the string to be extracted could be different

Column1	Column2	column3
BANK BO06	Ahsan has BANK BO06😄 0048030 NSW	BANK BO06:D
HETH HA10	Personal A HAS DEG HETH HA10:A 00020203032 CLARINDA VIC 3169	HETH HA10:A
UNLY UL21	Sonia has PO-U 0015 UNLY UL21-1 deposit 44454664645 Mitcham, SA	UNLY UL21-1

[Luke_C · Accepted solution]

Hi @ahsansalik 

Here's a solution using the string functions. The orange piece finds the starting location of column 1 in column 2. It is wrapped in a substring function that returns the the piece of column 2 starting with the value returned by the findstring function, extending to the length of column 1 + 2.

substring([Column2],findstring([Column2],[Column1]),length([Column1])+2)

[atcodedog05 · Accepted solution]

Hi @ahsansalik 

Here is a regex approach.

REGEX_Replace([Column2], ".*("+[Column1]+".{2})\s.*", "$1")

Hope this helps 🙂

[ahsansalik]

Thanks for your help i am getting this error

[Luke_C · Accepted solution]

Name the field in the formula tool

[atcodedog05 · Accepted solution]

Hi @ahsansalik 

You might not set column name in formula tool.

[ahsansalik · Accepted solution]

This Error Pops up once i apply the formula to my actual data with the original column names 

[ahsansalik]

Thanks a lot guys i am grateful for the help yayyy solved my first day at alteryx and i just learned a few good things 😄