Splitting Names

[ritesh_akh]

Hi,

Please help me to get a code for the following purpose. I have names in column A from 1 to 8000 and have a number of excel with the same type. I want to split the names in the following format:

If the name is of two words then it should split the name such that the first name goes under the column name "First Name", say it to be column B and the Last name to go in the fourth column named as "Last Name" say it to be column D. NOTHING GOES IN COLUMN "C" as that is for "Middle Name".

Eg:

Column A1 - Sam Jose
Column B1 - Sam
Column C1 - "**SHOULD BE BLANK**"
Column D1 - Jose

If the name is of Three words then:
first name in column B named "First Name"
Middle name in column C named "Middle Name"
Last Name in column D named as "Last Name"

Eg:

Column A1 - Sam Dave Jose
Column B1 - Sam
Column C1 - Dave
Column D1 - Jose

If the name is of four or more words then:
First Name in column B named "First Name"
Last Name in column D named as "Last Name"
Other words to go in column named "Extra1", "Extra2", "Extra3"

Eg:

Column A1 - Answers do not have Questions
Column B1 - Answers
Column C1 - "**SHOULD BE BLANK**"
Column D1 - Questions
Column E1 - do
Column F1 - not
Column G1 -have

Please Note:
Names to be split is in column A, column B be will contain only the "First Name", Column C will contain only the "middle name (If any)", column D will contain the last name and other words (for the names having more than 3 words) in column E, F, G...

Please help me on this.

Thanks in advance.

 

[VoG]

Data > Text to Columns, Delimited with Space as the delimiter would get you most of the way there.

 

[ritesh_akh]

Hi Peter,

Thanks for the advice, but that goes improper, not the way i want.

 

[Jonmo1]

You're asking quite a lot from a formula...

Is VBA code a viable solution?

 

[ritesh_akh]

I really think so, else there is a lot manual work. If you can suggest anything else, please let me know.

 

[Jonmo1]

Try this

Sub test()
Dim lr As Long, MyVals As Variant, i As Long, c As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each c In Range("A1:A" & lr)
    MyVals = Split(c)
    c.Offset(0, 1).Value = MyVals(0)
    c.Offset(0, 3).Value = MyVals(UBound(MyVals))
    Select Case UBound(MyVals)
        Case 2
            c.Offset(0, 2).Value = MyVals(1)
        Case Is > 2
            For i = 1 To UBound(MyVals) - 1
                c.Offset(0, i + 3).Value = MyVals(i)
            Next i
    End Select
Next c
End Sub

 

[ritesh_akh]

Hi Jon,

Sorry for the late reply. This code worked for me the way i wanted. Thanks