Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Create Postcode Area From Postcode Field

benmillea
7 - Meteor

Hi everyone,

 

I want to be able to create a postcode area field from a full postcode. The postcode area is the first one or two alpha characters of the postcode. Examples:

 

LS13 1NG - the postcode area would be "LS"

M2 3RG - the postcode area would be "M"

 

In Excel, I would use the following formula:

 

=IF(ISNUMBER(-MID(A1,2,1))=TRUE,LEFT(A1,1),LEFT(A1,2))

(where A1 = postcode field)

 

In Access, I would use:

IIf(IsNumeric(Mid([Postcode],2,1)),Left([Postcode],1),Left([Postcode],2))

 

I'm trying to replicate this in Alteryx, but I seem to be struggling with the MID function. Any suggestions please?

 

p.s. I'm very new to Alteryx, so apologies if this is ridiculously simple!

4 REPLIES 4
jamielaird
14 - Magnetar

Hi @benmillea 

 

Here's a simple approach using the Regex tool:

 

Screenshot 2019-05-15 at 16.59.03.png

 

If you're new to Regex this is a great resource: https://regexr.com/

benmillea
7 - Meteor

Wow! Thanks very much for the quick response @jamielaird. I've not come across Regex, so will look in to it. Is there something similar to pull out postcode sector (LS13 1 & M2 3) and district (LS13 & M2) from the postcode field? I have managed to do both using the formula function, but interested to know if Regex can do the lot?

 

Thanks again.

jamielaird
14 - Magnetar

Hi @benmillea 

 

Here's a great resource you can refer to: https://gisforthought.com/uk-postcode-breakdown-regex/

Thableaus
17 - Castor
17 - Castor

Hi @benmillea 

 

Just a heads up: The equivalent function for MID is Substring.

 

Substring(String, Start, length)

Start point is 0. 

 

Cheers,

Labels