Splitting Words in Excel

[Chanmal]

Dear All,

I have a list of information in one table e.g PaulSmith and I need to separate the First name in one column and the Surname in other column. There is no type of delimiter in these words, so I think that first a comma or a space need to be inserted before I split up the words. Kindly, could you please help me formulate this type of formula.

Thanks in Advance.

 

[Special-K99]

You haven't supplied much test data so the only obvious delimiter is the capital letter of the surname which raises the question are you liable to get

PaulCSmith
FredTRSmith
BertJamesEvans

in which case that solution won't work since you won't know how many middle initials there will be.

I guess you could try and identify the last capital letter in the name

As long as you don't have things like

PaulSmith Ba.
FredJones A.C.E.

 

[Comfy]

Slightly adapted a formula from here:

Daily Dose of Excel » Blog Archive » Find Position of First Capital Letter in a String

Excel 2010
	A	B	C	D
1	String	SurnameCapital	First Name	Surname
2	DanStevens	4	Dan	Stevens
3	JohnPhillips	5	John	Phillips
4	ShaneFord	6	Shane	Ford
Sheet1

Cell Formulas
Range	Formula
C2	=LEFT(A2,B2-1)
D2	=MID(A2,B2,LEN(A2)-B2+1)
B2	{=MATCH(TRUE,ABS(CODE(MID($A2,ROW($1:$255)+1,1))-77.5)<13,0)+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

Of course this will only work IF:

* Each word is Proper Case
* The only have one First Name
* The surname starts at the second Upper Case Character
* etc....