Splitting a Postcode

Hi all,

I have a worksheet that has a column with loads of postcodes (5000 altogther). I need to split these postcodes into their two parts - Now this wouldn't be a problem if they were all the same length I.E GU73BT can easily be split into GU7 3BT using Data->Text to columns.

Some of the postcodes I am dealing with are in this format; N57QB, whilst others are GU145TR. The thing with UK postcodes is that the second part of the split must contain 3 characters I.E the 7QB and 5TR.

I hope this makes sense and that there is some solution, what I ideally need to do is use the Text to columns function but inversley.....From the right to left!

I would do this manually but as I said there are over 5000 rows!

Thanks in advance

Rich

Have you tried using the MID function? Using the MID function allows you to extract a specified number of characters, beginning anywhere in the string you specify.

If you can sort the data by country, u can easily modify the MID funtion to extract the right amount of characters for the job.

Not sure if that's what you were getting at...

Assuming that your Post Code is in cell A1:

For the 1st part your formula would be LEFT(A1,LEN(A1)-3)

For the 2nd part your formula would be =RIGHT(A1,3)

Originally Posted by taylorm

Assuming that your Post Code is in cell A1:

For the 1st part your formula would be LEFT(A1,LEN(A1)-3)

For the 2nd part your formula would be =RIGHT(A1,3)

That's what I need!

Thanks

Right, there's a further complication now.....I need both parts of the postcode in one cell.....but with a space in between the two parts I.E. I need to join the GU7 from one cell to the 3BT in the other cell, but they need to have a space in between the two split parts......Hopefully that makes sense.

I've had a go myself by messing around with the formula but I can;t figure anything out.

Thanks

Originally Posted by clarkerm

Right, there's a further complication now.....I need both parts of the postcode in one cell.....but with a space in between the two parts I.E. I need to join the GU7 from one cell to the 3BT in the other cell, but they need to have a space in between the two split parts......Hopefully that makes sense.

I've had a go myself by messing around with the formula but I can;t figure anything out.

Thanks

=A1&" "&B1

oldchippy

Brilliant! I owe you guys lol

Another way to tackle this problem is to locate/find the space " " in between both halves of the post code and then insert it into a LEFT formula. E.g =find(" ",A1) for Post code EH11 4RT will return a value of 5 as the space " " is charachters in from the left. If you then subtract 1 you get the last letter of the first part of the post code e.g. =FIND(" ",A1)-1. If you then insert this into a LEFT formula =LEFT(A1,(FIND(" ",A1)-1))

Originally Posted by FlameRetired

Another way.

	A	B	C
1	GU73BT	GU7 3BT	=REPLACE(A1,LEN(A1)-2,0," ")
2	N57QB	N5 7QB

How do you avoid getting two spaces if you have some postcodes that already have a space? (If you’re trying to tidy a whole sheet of postcodes)

Thanks