# Cut UK postcode into separate field

1. ## 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.

2. ## Re: Cut UK postcode into separate field

Hi, welcome to the forum

Formulas can only affect the cell they reside in, they do not work on any other cell.

You will need to create another helper column and (probably) use Find/Replace to remove the PC into the new column

3. ## Re: Cut UK postcode into separate field

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.

4. ## Re: Cut UK postcode into separate field

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"

5. ## Re: Cut UK postcode into separate field

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

6. ## Re: Cut UK postcode into separate field

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.

7. ## Re: Cut UK postcode into separate field

Assuming you extract the post code from cell A2 into cell B2, you could use this in cell C2 (for example) to remove the post code:
Formula:
`Please Login or Register  to view this content.`
or, to lose the last comma, use:
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Cut UK postcode into separate field

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

9. ## Re: Cut UK postcode into separate field

With PowerQuery

10. ## Re: Cut UK postcode into separate field

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.

11. ## Re: Cut UK postcode into separate field

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.

12. ## Re: Cut UK postcode into separate field

You can use Power Query for

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

13. ## Re: Cut UK postcode into separate field

In B2

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

to remove the comma

14. ## Re: Cut UK postcode into separate field

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

15. ## Re: Cut UK postcode into separate field

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
If you did it already - ignore it.
Thank you.

16. ## Re: Cut UK postcode into separate field

You're welcome. Thanks for the rep.

17. ## Re: Cut UK postcode into separate field

Sorry, let's try that again

Assuming you extract the post code from cell A2 into cell B2, you could use this in cell C2 (for example) to remove the post code:
Formula:
`Please Login or Register  to view this content.`

or, to lose the last comma, use:
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)