community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Create Postcode Area From Postcode Field

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!

Alteryx Certified Partner

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/

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.

Alteryx Certified Partner

Hi @benmillea 

 

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

Alteryx Certified Partner

Hi @benmillea 

 

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

 

Substring(String, Start, length)

Start point is 0. 

 

Cheers,

Labels