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

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

Accepted answers

morr-co

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.

Screen Shot 2020-11-20 at 10.23.52 AM.png

address_cleansing.yxmd

All comments

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

UpdateAddress.yxmd

morr-co

Screen Shot 2020-11-20 at 10.23.52 AM.png

address_cleansing.yxmd

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 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 .

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703