how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Hi Frank
maybe not the cleanest formula, but it works:
Column A your Names
Column B: First Name:
=LEFT(A1,FIND(" ",A1)-1)
Column C: Middle Name:
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),"",MID(A1,LEN(B1)+2,LEN(A1)-
LEN(B1)-LEN(D1)-2))
Column D: Last Name:
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND("
",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
pay attention, the formulas are long, so they might wrap while
posting. each formula for each column has to be on one line!
I'm pretty sure, somebody has an easier solution, but until then...
hth
Carlo
On Dec 18, 2:46 pm, Frank Situmorang wrote:
Hello,
I want to finetuning my chruch records membership
With 500 members some names are 2 or 3 pieces words and now I want to put
them into separate names since I want to build a Access database instead of
excell
this is example of my problem
In excel iin one Cell
Rona Aprilia Gultom, I want to separate it into 3 Cell
Fuirst Name: Rona
Middle Name: Aprilia
Last Name: Gultom
My problem some is 2 pieces and some are 3 and the length of the name is
variable so I can not use Mid function in this matter.
What is certain is there is alwasy "space" between the words of the name
Can anyone help me how to do splitting this names?
Thanks in advance,
Frank
--
H. Frank Situmorang
|