Postcode Problem

[Borough]

I have a problem with a huge mailing list in Excel. The problem is that
the postcodes are in the wrong format. They are at present in the
format of NH129HS and I would like them to be NH12 9HS. All of my
postcodes are 8 characters in length and I just want to insert a space
as the 5th character. The postcodes are all in column J of my
spreadsheet.

Can anyone please help?

Thanks
Borough.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Freemini]

Borough
the following formula will produce your corrected Post Code. If your
postcode appears in J4 then type the following into K4 and then copy to
the rest of column K =LEFT(J4,4)&" "&MID(J4,5,3) this will put a space
between the "2" and "9" in your example. If you then copy column K and
use Paste special, Values only option your cell will contain you
amended Post Code in the correct format.

If you need to change the formula for 6 or 7 digit codes the formula
can be easily modified.

Mike



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------