Create an Address Key by Extracting only numbers from an address

Good morning folks,

I'm using address data in a project and I need to extract only the numbers from the given address. In other software packages I was able to use the INCLUDE or EXCLUDE function to allow me to arrive at a field with 'just numbers' or 'just letters'. I cannot seem to replicate this action in Alteryx and I have very little experience using REGEX. Any help is appreciated. The table below shows my address field and Address Key is my desired result.

Thanks,

George

Address Field	AddressKey
830 New Jersey Avenue, Brooklyn NY 11207	83011207
2344 Lorillard Place, Apt 3, Bronx NY 10458	2344310458
3333 101st Street, Corona NY 11368	333310111368
1415 Ave O, Brooklyn NY 11230	141511230

AddressKeySample.xlsx

Best Practices

Data Investigation

Accepted answers

Mathias_Nielsen

For what you are trying to do maybe you can just use the data cleansing tool as it can remove all the whitespace, letters and punctuation and you will be left with the result that you are looking for.

Just dublicate the column if you want to keep the original data also

Else LMK and i can help you with some Regex

2025-09-25_14-49-59.jpg

All comments

jrlindem

In this case, I would recommend REGEX to look for address patterns. I'm not good enough to rattle off regex by memory, but i'm sure others will chime in.

You could also do some fancy formulas by finding the numeric location of the "first open space" in the string and then using LEFT function to grab the leading numbers and then similar to grab the ending (zip code) numbers. Once you've got those, simply concatenate them together.

Depending on your comfort level, will dictate your approach. -Jay

Mathias_Nielsen

Just dublicate the column if you want to keep the original data also

Else LMK and i can help you with some Regex

2025-09-25_14-49-59.jpg

gfisch13

Thanks for responding @jrlindem. I really need to get up to speed on REGEX as I can see its power. I was trying to avoid doing multiple steps as you indicate because I know there's much simpler solutions. I also put this task out there because I love the input from the community and the creativity can help in other areas! Thanks again!

alexnajm

Fun one! @jrlindem is right that RegEx is best - here's a possible solution:

Create an Address Key by Extracting only numbers from an address.yxmd

alexnajm

I like @Mathias_Nielsen 's idea too!

aatalai

@gfisch13 here is a regex formula

REGEX_Replace([Address Field], "[^0-9]", "")

You can also do it with a data cleanse tool, take a look at the attached workflow for more explanation

Create an Address Key by Extracting only numbers from an address.yxmd

jrlindem

Three good options:

@Mathias_Nielsen with Cleanse Tool

@alexnajm / @aatalai with Regex

And here's a long-form formula sequence to get there (making assumptions about street number existing and zip code always being at the end):

Mathias_Nielsen

@jrlindem

I want to also join in with my Regex solution!

Duplicated the column and used Regex to replace all but the digits.

2025-09-25_15-02-30.jpg

gfisch13

Thank you all for your input. I'm testing each suggestion as its a great learning opportunity. As suggested a few times, the Data Cleansing Tool worked perfectly.

I've been away from Alteryx for a while being unemployed, but my new company has allowed me to introduce this technology, I'm just a bit rusty and the tools changed a bit.

Appreciate this community, great minds!!

jrlindem

@gfisch13 Glad you're staying engaged with the community. It is a great place!

One thing I wondered about the Data Cleanse tool... I know your output example turned "3333 101st Street, Corona NY 11368" into "333310111368" but would the spirit of what you're after also want to exclude the street name regardless if it's a number-name or not? In this case "101st". By removing just characters and leaving numbers, it includes a portion of the street name (the 101). Feels inconsistent to have that included? Not being critical at all of @Mathias_Nielsen as I do love that option; but I continue to wonder if that's consistent with what your ultimate goal is...

Anyway, just some additional musings. -Jay

gfisch13

@jrlindem, @Mathias_Nielsen

Good morning Jay, I certainly appreciate the conversation and I'm always looking for ways to improve my workflows. I've been using this technique for many years. Matching addresses is tough due to all the variations of words, street names, etc. I was at a fraud conference and did a breakout session with ACL and they taught this technique that leaves just the numbers to create a key. The biggest issue I've faced over the years is the variations in address formats. This method has allowed me to get past that while improving my match rate. It's probably not 100% accurate but I know it will work for my data as everything is in the same format coming out of our source systems. I wanted my company to buy the address normalization tool set but they wouldn't buy, so here I am. Happy to listen if you have additional ideas. Have a great day!!

WirkKarl

Hey! I’d use the REGEX tool for this. Something like REGEX_Replace([Address], "[^\d]", "") will strip out everything that’s not a number and leave you with just the digits. Super handy for generating an Address Key.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335