Adding spaces to Canadian postal code

**loren.silverman** · 03-01-2012, 05:32 PM

I have a spreadsheet that includes Canadian postal codes which, when correctly formatted are "XXX XXX". In my sheet they are all formatted "XXXXXX". How do I tell excel to add a space after the third character?

**daddylonglegs** · 03-01-2012, 05:34 PM

You could do that with a formula in another column, e.g. if postcodes start at A2 then use this formula in B2 copied down

=REPLACE(A2,4,0," ")

**NBVC** · 03-01-2012, 05:35 PM

in another column enter formula like:

=REPLACE(A2,4,0," ") where A2 contains a postal code entry.... and copy down for others.

You can then copy and paste special >> values over the original and delete the formula column.

**ConneXionLost** · 03-01-2012, 10:02 PM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Thread moved to General forum.