Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA inserting a space in a text | Excel Discussion (Misc queries) | |||
Adding a space in text | Excel Worksheet Functions | |||
Remove Space in Text | Excel Discussion (Misc queries) | |||
Seperating text if there's more than a one space between them | Excel Discussion (Misc queries) | |||
Find last space from the right of text | Excel Discussion (Misc queries) |