I am trying to compare several fields and replace the similarities with white space

[lbolin]

I am trying to clean up the Address filed by comparing it to the Location and Zip fields, If address has similarities it needs to be removed and replaced with white space. I have been looking in to doing it with regex but i am new to this and i am having trouble getting all the pieces together. This will nee to be done to 2000+ records 

EX:

Location	Address	Zip
Austin,Texas,United States	Billy Bob 4456 Bush Lane Austin,Texas 78746 United Statea	78746

End result

Address

4456 Bush Lane

[messi007]

Dear @lbolin 

Please check the attached workflow.

I used find and replace in order to replace the address with sapaces 🙂

below the input:

the output:

Hope that helps 🙂

Regards

[morr-co · Accepted solution]

Hello @lbolin : this one is definitely a little tricky - as working through data quality issues often is. I've attached one approach which removes exact word matches in the location and zip fields from the address fields. Hope this is helpful. 

[lbolin]

I seem to have not accounted for States that don't have space between the state and country. What would be the best way to account for that?

[lbolin]

I figured it out. I added a space before a captial letter then when the query was done i took them out. 

[lbolin]

I was reviewing my project this morning and I notices my record count went form 2,000 to 1,988 i have be combing throught and i think it is happening when everything's are joined together, I am not sure how that is happening .