# 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:JaneDoeMaryannSmithRobertWhiteIs there a way to separate into a first name and last name column?Thanks - Alice

See More: Text to Columns based on Uppercase

#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))))-1It 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 thename was found here:http://www.excelbanter.com/showthre...I simply modified it to fit.MIKE

Report •

#2
October 22, 2012 at 17:49:11
 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-EnterThen 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:MaryJoMcGillicuttyBillO'ReillyRobertGriffenIIIClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#3
October 22, 2012 at 18:28:51
 alicecBe aware that the formula I posted will Fail if there are more thenTwo 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 namenot 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 ifthe First & Last Name have the same letter, IE MickeyMousealso if the First name does NOT start with a Capital, IE mickeyMouseMIKEhttp://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
 alicecBe 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 D1DerbyDad's will give you MaryMcNeither one of these formula is perfect so you will still need to do some cleanup.MIKEhttp://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 •