Concatenating Fields

Hello Alteryx Community,

I'm trying to concatenate two fields together to make 1 address line, which I know how to do if the data is in the right fields. However the data I want to use is split across three fields but I don't to use all of it.

In the screen grab below, I want to concatenate the contents of the building number field with the Thoroughfare_and_descriptor field (street name).

However some of the building numbers are in Building Name field. Where this happens I only want to concatenate the numbers and not the names with the Thoroughfare_and_descriptor field (street name).

Any suggestions on how I can do this please?

Address concat.JPG

Thanks in advance,

Datasets

Preparation

Accepted answers

fmvizcaino

Hi @JamesDB ,

Attached is an example showing how to divide your dataset using regex.

Let me know if this works for you.

Best,

Fernando Vizcaino

2021-03-31 Concatenating Fields.yxmd

echuong1

You can check to see if the building name field contains numbers with regex match.

I also included a condition to check if the building number contains numbers, because some of them are missing a number in both building name and number.

New Workflow14.yxmd

All comments

fmvizcaino

Hi @JamesDB ,

Attached is an example showing how to divide your dataset using regex.

Let me know if this works for you.

Best,

Fernando Vizcaino

2021-03-31 Concatenating Fields.yxmd

echuong1

You can check to see if the building name field contains numbers with regex match.

I also included a condition to check if the building number contains numbers, because some of them are missing a number in both building name and number.

New Workflow14.yxmd

Jonathan-Sherman

Who doesn't love a one tool solution @echuong1! It always amazes me just how flexible the formula tool is!

JamesDB

Hi Fernando,

This appears to work for me, just testing it out now on the full dataset.

Many thanks.

JamesDB

Hi there,

Thank-you for your help

I've tweaked your solution slightly. As it was the completed field posed an additional issue. This is because a address line 1 of an address can be either a building name or a building number+thoroughfare_and_descriptor.

I've changed it so that the complete field only contains building number+thoroughfare_and_descriptor.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703