topic Extracting UK Postcodes in to new column in DAX Commands and Tips

Extracting UK Postcodes in to new column

richardburling — Wed, 30 Jun 2021 13:28:43 GMT

I have a dataset which contains full addresses but I need the postcode extracted in to a seperate column. I have managed this in excel using this formula:-

=RIGHT(SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1),LEN(B2)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1)))

The address is in the following format:-

221b Baker St, London, NW1 6XE

I have searched through the forums and so far cant find anything that meets the requirement. I have seen his done by breaking out the address using the delimiter functions in the Quary Editor and then combining the two parts of the postcode to create a full postcode but ideally I would like a column for the full address and a seperate one for the postcode.

I hope someone can help.

Thanks

Richard.

Re: Extracting UK Postcodes in to new column

CNENFRNL — Wed, 30 Jun 2021 22:06:17 GMT

Wikipedia introduces postcodes in the UK in detail; interesting enough, regular expressions for validation are also provided.

https://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom

So, it's fairly easy to extract postcodes with above-mentioned regex,

Re: Extracting UK Postcodes in to new column

richardburling — Thu, 01 Jul 2021 07:45:30 GMT

CNENFRNL thanks for taking the time and making a suggestion. Unfortuntaley this isnt what I am looking for.

Basically I think a spreadsheet with 2000+ addresses and would like to seperate the postcode from the address string. Essentially I need the Query Editor to extract only the postcode from the address string and put it in a new column. It cant have an spaces or commas in the column either. Obvioulsy postcodes come in various characters length so its not just a case of using the 'Text.end([Address],8)' as this brings back all the last eight characters.

I hope that makes sense.

Richard

Re: Extracting UK Postcodes in to new column

geebers — Tue, 21 Jan 2025 16:02:30 GMT

This is just what I want, how do i make if fit my scenario?