Need formula to put a space in postcode cell

[BoldB]

Hi,

Is there a formula that can look to see if there is a space in the data in a cell and if there is not, insert a space 3 digits from the end.

So the postcodes TA1 2BN and LS12 8PO would be left as they are and TA12BN and LS128PO would become TA1 2BN and LS12 8PO?

So the table would end up like this:

Data	Formula
TA1 2BN	TA1 2BN
LS12 8PO	LS12 8PO
TA12BN	TA1 2BN
LS128PO	LS12 8PO

<tbody>
</tbody>

Kind Regards
Steven

<tbody>
</tbody>

 

[BobUmlas]

=if(mid(a1,len(a1)-4,1)=" ",a1,left(a1,len(a1)-3)&" "&right(a1,3))

 

[raj2206]

Hey,
Thanks for writing!

You can use the following formula:
=IF(IFERROR(FIND(" ",A2)>0,0),A2,REPLACE(A2,LEN(A2)-2,0," "))

Change the referencing as per the requirement.

Hope this will help,

Raj

 

[BoldB]

Thanks for the help Raj, Perfect

 

[Firefly2012]

An alternative:

Excel 2007
	A	B
1	SO236NZ	SO23 6NZ
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(REPLACE(A1,LEN(A1)-2,0," "))

 

[raj2206]

Welcome, any time !!