Replace Names from one dataset with names from another

[yashaschowdary]

Hello,

I have 2 datasets and I am trying to Join them with Branch name, however the names on one data set is written differently from the other. 

For example: 

Dataset 1	Dataset 2
Kent Retail	Kent
Brighton CHR	Brighton
Boltley CMB	Boltley

I am not able to match the 2 data sets as the names are different. I want to change the names on dataset 1 to be the same as dataset 2. I can not manually change the names as there are over 100 branches. I tried using the 'Find Replace' tool but it did not work. Please could you guys help me solve this issue. 

[Kenda · Accepted solution]

Hi @yashaschowdary 

There are a couple ways you could do this. The first that comes to mind would be to use the Append Fields tool to create a row for every combination of your two datasets. Then, just add a Filter tool to only keep the ones where data set 1 contains the test in data set 2. See the attached workflow. Hope this helps!

[adamorse]

Is it always the case that first word of the name in Dataset 1 matches the name in Dataset 2?

If so, you could use some Regex to extract the first word in dataset 1 using a formula tool (see screenshot) and then join on the result.

RegEx interpretation

^ - beginning of string

([^\s]*) - group 1 consists of any number of consecutive characters that aren't spaces

\s - space

.* - any characters

$ - end of string