Concatenating Fields

[JamesDB]

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? 

Thanks in advance,

[fmvizcaino · Accepted solution]

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

[echuong1 · Accepted solution]

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.

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