formula to add space within text

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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.
 
Assuming your data is in A1:
B1: =concatenate(left(a1,3)," ",right(a1,3)) separate the quotes with a space

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

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

HTH,
Paul
 
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
 
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
 
Back
Top