how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells

[Frank Situmorang]

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

 

[carlo]

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

 

[Kalpesh]

Frank,

You can use Split function to break the string into pieces seperated
by space.

So, a string with Rone Aprilia Gultom will return an array with length
3
e.g. data = Split("Rona Aprilia Gultom", " ")
data(0) - will return Rona
data(1) - will return Aprilia
data(2) - will return Gultom

The length of array will change depending upon the spaces inside the
name.
I am assuming, things are seperated by space.

HTH
Kalpesh

 

[Frank Situmorang]

Thanks Kalpesh for your quick response, however I have tried to look all
excel function, I can not found split function, could you explain again on
how can I handle it?

Thanks very much

 

[carlo]

Kalpesh's solution is in VBA, because you posted it in the programming
section.
I figured that you wanted to achieve this with formulas, that's where
my approach comes from.

hth

Carlo

 

[Gary Keramidas]

just to expand on kalpesh's response, his solution is vba since this is a
programming newsgroup.

try this code. it assumes your names are in column A starting in Row1 on sheet1
and the names are split in columns B, C & D in the same row

just change the references to whatever you need

Sub split_text()
Dim data As Variant
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
data = Split(Range("A" & i).Value, " ")
ws.Range("B" & i) = data(0)
If UBound(data) = 2 Then
ws.Range("C" & i) = data(1)
ws.Range("D" & i) = data(2)
Else
ws.Range("D" & i) = data(1)
End If
Next
End Sub

 

[Frank Situmorang]

Thank you Carlo, you are right, I do not use VBA and not to familiar with it,
I am an accountant in fact, It works now for the first name using your
formula, but now I am trying to figure out how Middle and last name formula
can work.

Thank you very much, if you could explain a bit more. How if we use RIGHT
function and always refer to the same Cell A1

 

[Frank Situmorang]

Gary:

Could you educate me how can I use VBA to do it. Should I assin a kind of
macro, where is the control

Thanks in advance

 

[Gary Keramidas]

in excel, press alt-F11 to enter the vb editor
then control-R to view the explorer window if it's not visible

you can either double click the sheet name and paste the code in the window that
opens or right click in a black area below the sheets names, choose insert then
module. paste the code there

once you've done that, you can run the code from the vb window, or close it,
press alt-F8 and run the macro from there.


--


Gary

Gary:

Could you educate me how can I use VBA to do it. Should I assin a kind of
macro, where is the control

Thanks in advance

 

[Frank Situmorang]

Carlo,

I need to tell you that the names some times there is only 2 pieces and some
times 3 pieces, how can we cope with this situation

Thanks in advance

 

[Frank Situmorang]

Thank you Gary, I will give a try

 

[Frank Situmorang]

Thanks very much Carlo, it works ok now for me

 

[Gord Dibben]

Frank

Maybe you could work with Data>Text to Columns>Delimited by space.


Gord Dibben MS Excel MVP

 

[Frank Situmorang]

Thanks very much Gord, you gave me a simple way.

 

[carlo]

Hi Frank

just wanted to tell you, that Gord's solution is pretty neat, although
you have to pay attention.
The way your data is set up right now, the lastname of your
2piecenames and the middlename of your 3piecenames will be in the same
column....maybe not the best if you want to export the data to a
database, as you said in your first post.

In my first post i gave you formulas for all 3 columns, which will put
the last name always in column d and fill out column c (middlename)
only if there is a middlename.

Cheers Carlo

If you have other questions, just ask.