Separating first and last names - vba

[lordriel]

I've seen several methods for separating names, or text, from one cell to two or more. However, none of them give a vba answer.

How would I code it, were it to be done when hitting a command button?

 

[NateO]

Hello,

That really depends on how complex your names are. Is it always two names with a space? If so, record a Macro using Text-to-columns (data). Use a space as your delimiter.

If you have middle names, and suffixes, etc... You're going to have to work quite a bit harder, I'm afraid... But, that has been posted here, to date.

 

[lordriel]

This works, and would be functional if I change up a few other levels of code - which I may have to do.

I am clocking the data from one cell on one sheet into two cells on another in another workbook.

I tried the following code, with mixed results (probably because I'm not understanding everything it's doing):

lname = Right(Worksheets("Import").Cells(j, 2), Len(Worksheets("Import").Cells(j, 2)) - InStrRev(Worksheets("Import").Cells(j, 2), " "))

FName = Left(Worksheets("Import").Cells(j, 2), Len(Trim(Worksheets("Import").Cells(j, 2))) - InStrRev(Worksheets("Import").Cells(j, 2), Trim(lname)))

Worksheets("import").Cells(j, 12) = lname
Worksheets("import").Cells(j, 13) = FName


(Data in the original field is First Name Last Name)

This gave me the last name, clean and clear. But the first name is either truncated or includes parts of the last name. I know it's because I'm defining the length of the result based on the length of the last name...but haven't noodled out how to remedy it.

Thanks for looking...

lr

 

[NateO]

Text-to-Columns is an extremely fast algorithm, why not use it, and then cut and paste wherever you want, with code (you can disable ScreenUpdating while it processes)? That's how you'd want to do it by hand, or by code.

 

[VoG]

With code and assuming that the names are in column A

Sub SplitNames()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next i
End Sub

 

[lordriel]

Like a charm!

Thanks to both of you for your help!

 

[NateO]

Another approach, although very similar, would be to use an Array instead of looping through Ranges, toggle your ScreenUpdating, as mentioned, and use a String Array, e.g.,

Sub foo()
Dim varArr As Variant
Dim i As Long, strArr() As String
Application.ScreenUpdating = False
With Worksheets(1)
    Let varArr = .Range(.Cells(1, 1), _
        .Cells(.Rows.Count, 1).End(xlUp)).Value
    If IsArray(varArr) Then
        For i = LBound(varArr, 1) To UBound(varArr, 1)
            Let strArr() = Split(varArr(i, 1))
            Let .Cells(i, 2).Resize( _
                , UBound(strArr) + 1).Value = strArr
            Erase strArr
        Next
    Else: .Cells(1, 2).Resize(, 2).Value = Split(varArr)
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Like a charm!

I'm assuming you are referring to VoG's code (which does work like a charm) but I'm wondering what happened to Nate's suggestion of using excel's built-in functionality for this sort of job, namely 'Text to Columns'?

If VoG's looping code does the job, then as far as I can see this 'one-hit' Text-to-Columns method should do too. Assumes data starts in cell A1.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Split_Names()<br>    Columns("A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _<br>        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[NateO]

That's exactly what I meant, Peter - Excel has native functionality (nice functionality) to do this, and it's mind-boggling fast.

Try that on 60,000 rows of data (fast). Whoever wrote that algorithm gets an A+ from me.

 

[pratmacro]

With code and assuming that the names are in column A

Sub SplitNames()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next i
End Sub

This code very good. but i want only first name in one variable but sometime happen some columns having only first name and my code not able to run. for example if one cell having "Pratik is in this country" i want only "Pratik" so i write code "d = Left(d, InStr(d, " ") - 1)" but in some cell having only one name like "Pratik" hence my coding giving error. can you help me to get only first name in a variable