What tool is best used to remove suite from an address?

[nberumen]

Hello All 🖐!

I was trying to use a filter tool to filter out suite numbers from the Billing City / District column by using a contains formula ex. "Contains([Billing Address], "Suite") OR Contains([Billing Address], "Ste" 

but it also brought in data that did not have suite numbers but had "Ste" in the word as shown in the table below. What is the best approach to not catch those cities containing "ste" in them and the best way to properly parse the addresses to the appropriate column?

Record ID	Billing Name	Billing Address	Billing City / District	Billing State / Province	Billing Country
317	Jump	333 Center Park	STE 333	Lineville CA 88822	United States
103	Tech	5 Doe Drive	Suite M	Mouth Junction ND 11111	United States
310	Brains	999 West Hill Drive, Suite 222	Foster Plains CO 99999	United States
523	Stroll	1111 Firm Way	Westminster MA 55555	United States
118	Bop	555 Penny Drive	Feasterville PA 11111	United States
354	MD	13 Land Ave	Lancaster NM	United States

Desired Outcome: 

Record ID	Billing Name	Billing Address	Suite	Billing City / District	Billing State / Province	Billing Zip code	Billing Country
317	Jump	333 Center Park	STE 333	Lineville 8	CA	8822	United States
103	Tech	5 Doe Drive	Suite M	Mouth Junction	ND	11111	United States
310	Brains	99 West Hill Drive	Suite 222	Foster Plains	CO	99999	United States

[IraWatt · Accepted solution]

Hey @nberumen,

One way to achieve this is using Regex. I used this pattern in a REGEX_Match function:

.*\bSTE\b.*

This will return true if STE appears as a word in the text.

If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[nberumen]

Thank you!!

Such a simple solution and it works great, I really appreciate the help!