Cut UK postcode into separate field

Hi. I have a problem with cutting/trimming UK postcodes from the end of an address into their own column. I have found a few solutions online that allows me to extract postcodes into their own column, but the issue I'm having is that the postcode remains at the end of the address. I need to parse the remaining addresses into their own columns, but the postcode must all be under the same column. Example:

1 Street View, Town, Area, County, ER2 5AW
2 Avenue Way, Town, County, STW 5B
3 High Close, Town, TU 5RW

As you can see the addresses and postcodes are irregular, and I have found a solution to move the postcode to it's own column, but I need it to cut from the end of the address. The solution to move the postcode is as follows:

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

Does anyone know how I can amend this formula to CUT the postcode away from the address into it's own field. Any help would very much be appreciated. Thank you.

Of course, I should have realised the formula doesn't effect the column it's referencing. Thank you for this tip. Will a find and replace be able to pick up irregular postcodes? Sorry, I'm quite new to excel, and I need to figure this out as it makes up a large part of my new job.

most of use won't be able too simple because we do not live in the UK and have no idea how a regular postcode looks like. for me a "regular postcode" looks like "1234 AB" and I'm sure that is not what a UK one looks like

So in order to help you, the easiest way would be to make an example file with test data
and tell us what a regular postcode looks like so we can test for "irregularities"

Thanks for your reply. I've attached an image explaining what I'm trying to do. There are only 4 records in my example, but this is quite typical of the type of data that I work with on a daily basis.

Thanks.Example.JPG

Attach a sample workbook (not image) as we cannot work with images nor are we going to type in your data.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Sorry, I did want to upload the workbook but wasn't sure how, thanks for telling me. The data should be attached now.

With PowerQuery

Thank you TMS. The first formula did produce what I need, although with the comma. I'm not sure why, but the second formula produced results where the postcode was still at the end.

Thanks Sandy. How to you achieve this? I have 11,000 records to get done first thing tomorrow morning, and it would be great to know how you separated the postcode out from the address.

You can use Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What is PowerQuery? see here: Getting Started with PowerQuery (Get&Transform) in Excel

and split column by right most delimiter (comma in this case)

In B2

=SUBSTITUTE(A2,", " &C2,"")

to remove the comma

Thank you very much John, this is a big help. And thanks to all who contributed.

If that takes care of your original question, & to say Thanks, please

1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
   then
2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.

If you did it already - ignore it.
Thank you.