Solved Text to Columns based on Uppercase

October 22, 2012 at 14:21:29
Specs: Windows XP
If I have data that needs to be separated into columns that looks like this:
JaneDoe
MaryannSmith
RobertWhite

Is there a way to separate into a first name and last name column?

Thanks - Alice


See More: Text to Columns based on Uppercase

Report •

#1
October 22, 2012 at 16:02:28
Try this:

With your data like:

      A           B     C         D
1) JaneDoe
2) MaryannSmith
3) RobertWhite

In Cell B1 enter the formula:

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$1 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("A$1:A"&LEN(A1))))-1

It is a very long formula, so Copy & Paste from here.
Drag down as many rows as needed.

Your data should now look like:

      A           B     C         D
1) JaneDoe        5
2) MaryannSmith   8
3) RobertWhite    7

Column B has the number position of the Capital letter of the last name.

Now in cell C1 enter the formula:

=LEFT(A1,B1-1)

In Cell D1 enter the formula:

=RIGHT(A1,LEN(A1)-B1+1)

Your data should now look like:

      A         B     C         D 
1)JaneDoe       5   Jane      Doe
2)MaryannSmith  8   Maryann   Smith
3)RobertWhite   7   Robert    White

Unfortunately I can't take credit for all of this,
the formula that does the work of spliting up the
name was found here:

http://www.excelbanter.com/showthre...

I simply modified it to fit.

MIKE

http://www.skeptic.com/


Report •

#2
October 22, 2012 at 17:49:11
✔ Best Answer
A shorter version is this Array Formula placed in B1 to extract the first name from A1:

=LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

Confirmed with Ctrl-Shift-Enter

Then this non-array formula in C1 for the last name:

=REPLACE(A1,1,LEN(B1),"")

Credit goes to:

http://www.ozgrid.com/forum/showthr...

Things get much,much more complicated if you have names like:

MaryJoMcGillicutty
BillO'Reilly
RobertGriffenIII

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 22, 2012 at 18:28:51
alicec
Be aware that the formula I posted will Fail if there are more then
Two capitals in a name, so MikeMcConaghy (3 caps) will fail,
also if the First name does NOT start with a Capital.

This updated formula will solve the second problem, of the first name
not having a capital.

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$2 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("A$2:A"&LEN(A1))))


DerbyDad03,
just a heads up, the Array Formula for the first name will Fail if
the First & Last Name have the same letter, IE MickeyMouse
also if the First name does NOT start with a Capital, IE mickeyMouse

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 22, 2012 at 18:29:52
A Million Thank Yous!
Both of these solutions were incredible. I used the second one only because of the situation of 3 uppercase letters (MaryMcNeil or JohnMcManus).

I really appreciate this website and you timely response. I'll spread the word...
Alice


Report •

#5
October 22, 2012 at 18:40:31
alicec

Be aware that the name MaryMcNeil will FAIL on both mine & DerbyDad03's formulas.

Mine will give you the full name in C1 and a #VALUE Error D1

DerbyDad's will give you MaryMc

Neither one of these formula is perfect so you will still need to do some cleanup.

MIKE

http://www.skeptic.com/


Report •

#6
October 22, 2012 at 18:46:03
Mike,
It will be a tremendous help, I will be able to do the random clean-up.

Thank you and DerbyDad for answering this for me. Maybe some day I can answer a question for you! (It will not be anything to do with computers, haha)

Thanks again.


Report •

Ask Question