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!
Solved! Go to Solution.
Hi @benmillea
Here's a simple approach using the Regex tool:
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.
Hi @benmillea
Here's a great resource you can refer to: https://gisforthought.com/uk-postcode-breakdown-regex/
Hi @benmillea
Just a heads up: The equivalent function for MID is Substring.
Substring(String, Start, length)
Start point is 0.
Cheers,