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

Alteryx designer Discussions

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

Create Postcode Area From Postcode Field

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!

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/

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.

Highlighted
Alteryx Certified Partner

Hi @benmillea 

 

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

Alteryx Certified Partner
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