formula to add space within text

[Jennifer]

I am using Microsoft Excel 2003 and I was wondering if there is a formula
that will add a space in the middle of text. For example I have a column of
postal codes that look like this N6C1V9 and I would like them to look like
this N6C 1V9 instead.

 

[Leon]

Assuming your data is in A1:
B1: =concatenate(left(a1,3)," ",right(a1,3)) separate the quotes with a space

Leon

 

[PCLIVE]

With your data in A1, if they are all 6 charaters in length, then:

=LEFT(A1,3)&" "&RIGHT(A1,3)

HTH,
Paul

 

[Rick Rothstein \(MVP - VB\)]

I'm under the impression that for those type of postal codes, the number of
characters in front of the space can vary, but the number of characters
after it will always be 3. If that is correct, then use this formula...

=REPLACE(A1,LEN(A1)-2,0," ")

Of course, you can use the above formula if your postal codes are always 6
characters long; but, if that is the case, you can use this shorter
variation...

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

Rick

 

[Jennifer]

Thanks for all your help, Leon, Paul and Rick! All three of these formulas
work great!

 

[Gord Dibben]

Look like 6-character Canadian Postal Codes.

If true, I have some code that will allow you to enter a PC as n6c1v9 and return
N6C 1V9

Won't do you any good after the fact but for future entry??


Gord Dibben MS Excel MVP