How do I separate text without a space?
 

In column A I have 10 different names listed. For example, in cell A1 I have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

How do I separate text without a space?
 

Try the following Array formula

=LEFT(A1,MATCH(TRUE,EXACT(MID(A1,ROW(INDIRECT("2:" &LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A 1))),1))),0))&"
"&MID(A1,MATCH(TRUE,EXACT(MID(A1,ROW(INDIRECT("2:" &LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A 1))),1))),0)+1,LEN(A1))

Watch out for line breaks -- the formula should be all on one line in the
cell. This is an array formula, so you must press CTRL+SHIFT+ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula enclosed in
curly braces {}.

Do not change the "2:" row references. They need to be "2:" regardless of
the row(s) in which the formula is entered.

See www.cpearson.com/excel/array.htm for more info about array formulas.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"trainer07" wrote in message
...
In column A I have 10 different names listed. For example, in cell A1 I
have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

How do I separate text without a space?
 

I noticed that the news client broke the line on a space character in the
formula. It needs to be a space " ", not an empty string "" as it might
appear in the message.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
Try the following Array formula

=LEFT(A1,MATCH(TRUE,EXACT(MID(A1,ROW(INDIRECT("2:" &LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A 1))),1))),0))&"
"&MID(A1,MATCH(TRUE,EXACT(MID(A1,ROW(INDIRECT("2:" &LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A 1))),1))),0)+1,LEN(A1))

Watch out for line breaks -- the formula should be all on one line in the
cell. This is an array formula, so you must press CTRL+SHIFT+ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula enclosed in
curly braces {}.

Do not change the "2:" row references. They need to be "2:" regardless of
the row(s) in which the formula is entered.

See www.cpearson.com/excel/array.htm for more info about array formulas.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"trainer07" wrote in message
...
In column A I have 10 different names listed. For example, in cell A1 I
have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

How do I separate text without a space?
 

With only 10 names to fix, it would be easiest to just edit the cells and change
them to what you need.

trainer07 wrote:

In column A I have 10 different names listed. For example, in cell A1 I have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

--

Dave Peterson

How do I separate text without a space?
 

On Tue, 6 Mar 2007 16:01:13 -0800, trainer07
wrote:

In column A I have 10 different names listed. For example, in cell A1 I have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

Simple method.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the Regular Expression formula:

=REGEX.SUBSTITUTE(A1,"([A-Z])"," [1]",2)


--ron

How do I separate text without a space?
 

On Tue, 6 Mar 2007 16:01:13 -0800, trainer07
wrote:

In column A I have 10 different names listed. For example, in cell A1 I have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?


More complicated method:

Use this *array* entered formula:

=LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW(
INDIRECT("2:"&LEN(A1))),1))<=90,0))&" "&
MID(A1,1+MATCH(TRUE,CODE(MID(A1,ROW(
INDIRECT("2:"&LEN(A1))),1))<=90,0),255)

To enter an *array* formula, after entering the formula into the formula bar,
hold down <ctrl<shift while you hit <enter. Excel will place braces {...}
around the formula.
--ron

How do I separate text without a space?
 

And if you had 10 names that showed up a bunch of times, I'd do 10 edit|replaces
to fix each occurrence of the name.

Dave Peterson wrote:

With only 10 names to fix, it would be easiest to just edit the cells and change
them to what you need.

trainer07 wrote:

In column A I have 10 different names listed. For example, in cell A1 I have
the name, JohnDoe. I would like to insert a space between the first and
last name. How would I do this for all the cells?

--

Dave Peterson

--

Dave Peterson