Help needed to amend the format of certain Post Codes

Morning,

And happy christmas,

I need some help. I have inherited a report from a previous Analyst. It is basically a complete system extract for a specific insurance company we deal with. Our extract has lots of formulas to amend the format of the columns as the entire Excel extract is then saved in Text format as the specific insurer needs it in Text so they can fit it into their own template their end. It is the length of certain postcodes where we are having an issue. Via Cognos my queries give me the Excel extract with no issues. However the spreadsheet I have inherited uses the following formula =IF(H3="","",SUBSTITUTE('Cognos Report'!I2," "," ")&REPT(" ",8-LEN(SUBSTITUTE('Cognos Report'!I2," "," ")))) to populate the Text template with the Post Code. Currently this works fine for the formatting and leaves a space in between the two blocks of postcodes. So CB6 2WU shows correctly on the Template as CB6 3WU. However the insurer has now requested that for London post codes (where there are only 5 digits - W1 5ST for example) they want a 2nd space in between the two blocks of digits, yet postcodes with more than 5 digits need to remain as they are. This is because their template is falling over with a single space between them.

Hopefully that is as clear as mud. I would prefer not to mess around with the existing formula too much. But if anyone can think of a better logic to it, or an amendment that will do the trick - I would be very happy.

Could you put in another IF statement that checks the number of characters in the cell, if less than 7 then substitute " " for " "

Used 7 as the outside London post code has 7 characters.

Windy