Extracting UK Postcodes in to new column

[richardburling]

Hi

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.  

[geebers]

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

[CNENFRNL]

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,

let
    RE = (regex as text, str as text) =>
    let
        html =
            "<script>
            var regex = " & regex & ";
            var str = """ & str & """;
            var res = str.match(regex);
            document.write(res)
            </script>",
        res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxCsIwFAXQX7lkztBEcE9t6aA4WCRCyBDJoxTTF0hT/H2Lg4P74TgntFZPtOFFBWOVuGSOmSWuVuH46IWXTqgGXX7zzNNOCtGPYbTKQBvzZUOhUHHb1pVS+pf2pFocukGi5ykFjhJ3nitFnPc35kV4/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Addr = _t]),
    #"Added Custom" = Table.AddColumn(Source, "P.C.", each RE("/[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}/gi", [Addr]))
in
    #"Added Custom"

[richardburling]

@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