How do I separate text without a space?

T

trainer07

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?
 
C

Chip Pearson

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(A1))),1))),0))&"
"&MID(A1,MATCH(TRUE,EXACT(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),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)
 
C

Chip Pearson

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)
 
D

Dave Peterson

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

Ron Rosenfeld

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?
Click to expand...

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
 
R

Ron Rosenfeld

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?
Click to expand...


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
 
D

Dave Peterson

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