formula to add space within text
 

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.
--
Jennifer

formula to add space within text
 

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

Leon

"Jennifer" wrote:

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.
--
Jennifer

formula to add space within text
 

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

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

HTH,
Paul

--

"Jennifer" wrote in message
...
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.
--
Jennifer

formula to add space within text
 

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" wrote in message
...
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.
--
Jennifer

formula to add space within text
 

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


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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.
--
Jennifer

formula to add space within text
 

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

On Thu, 10 Apr 2008 07:24:00 -0700, Jennifer
wrote:

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.

formula to add space within text
 

Hi Rick,

I have a string like this "Text/Text/Text/Text" Now i want it to convert like this "Text / Text / Text / Text" in excel 2010

Please help. Thanks in advance.

formula to add space within text
 

Il 30/06/2015, ha detto :
Hi Rick,

I have a string like this "Text/Text/Text/Text" Now i want it to convert
like this "Text / Text / Text / Text" in excel 2010

Please help. Thanks in advance.

Hi,
try
=SUBSTITUTE("Text/Text/Text/Text","/"," / ")

--
Ciao! :)
Maurizio