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

Question

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

Column1Column2column3BANK BO06Ahsan has BANK BO06😄 0048030 NSWBANK BO06:DHETH HA10Personal A HAS DEG HETH HA10:A 00020203032 CLARINDA VIC 3169HETH HA10:AUNLY UL21Sonia has PO-U 0015 UNLY UL21-1 deposit 44454664645 Mitcham, SAUNLY UL21-1

Luke_C · Accepted Answer

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 Answer

Hi @ahsansalik

Here is a regex approach.

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

Hope this helps 🙂

Luke_C · Accepted Answer

Name the field in the formula tool

atcodedog05 · Accepted Answer

Hi @ahsansalik

You might not set column name in formula tool.

ahsansalik · Accepted Answer

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