Dynamic find and replace substring in field 1 with value of field 2 on record level

[Gudo]

Hi guru's,

I am in the process of cleaning up customer data in our system. One of the issues I have run into is that at times the last name field contains the full customer name ('John Smith') and the first name field has the first name value as well ('John'). In reporting this will concatenate wrong ('John Smith, John').

I am looking for a method where I can use the value of first name and see if this is a substring of last name and then remove this substring in last name. So basically getting 'Smith' and 'John' in separate fields.

I have tried the find and replace tool. However this does not seem to work on record level, but rather over all records. All first name strings will be compared with all last names strings and if any match is found that substring will be removed. As a result records that had a last name 'John Smith' but were the first name was empty, are changed to 'Smith'. I would like the logic to work at record level only.

Could anyone assist me in this?

[wdavis]

Hi @Gudo 

You could use the Formula - GetWord([Last Name], CountWords([Last Name])-1) within the Formula tool.

This will count the number of words within your last name field and make sure to only take the last one.

Would this work for you?

Thanks

Will

[joshuaburkhow]

I put this into a workflow so you can see how it works clearly on a record level. There are a couple ways to do this but this is probably the simplest way 😉

[Jonathan-Sherman · Accepted solution]

Hi @Gudo,

I'd go about this in a slightly different way and simply use the following formula:

TRIM(Replace([Last Name], [First Name], ''))

This would take an input like this:

And finish with an output of:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download.

Regards,

Jonathan

[Gudo]

Thanks for the suggestion Will. Unfortunately it only does part of the trick. The order in which the words in the last name is put varies and sometimes you cannot distinguish the words (Johnsmith). 

[Gudo]

John,

This seems to work for my problem. The test cases I have all turn out as they should. I will have our testers go through my output next week and see if they can still manage to find or create cases in which this logic does not work.

Thanks!

[Jonathan-Sherman]

Great, feel free to post back on this thread if you have any further issues on it @Gudo!