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.