VBA : Insert "LEFT" function

[ravi4ever]

I want this function with the help of VBA..

To Get First Name =LEFT(A2,FIND(" ",A2)-1)
To Get Last Name =RIGHT(A2,LEN(A2)-FIND(" ",A2))
A2 is the cell where Full Name is..

After googling I understood that we cannot WorksheetFunction but don't know why the below code isn't working.. please assist..

Dim LastRow As Long
Dim f As String

LastRow = Range("A1048576").End(xlUp).Row

For i = 2 To LastRow
    If Cells(i, 1).Value <> "" Then
    f = "=LEFT(A" & i & ")" & ",FIND(""""," & "A" & i & ")-1)"
    Rng = "B" & i
    Range(Rng).Value = f
    Cells(i, 2) = f
    Cells(i, 2).Value = f
    
    End If
Next i

 

[MrKowz]

When trying to extract a first name and last name, one thing you need to watch out for is middle name(s). Try the following code:

Public Sub GetFirstLastName()
Dim LastRow As Long, _
    i       As Long, _
    tmp     As Variant

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    If Cells(i, 1).Value <> "" Then
        tmp = Split(Range("A" & i).Value, " ")
        Range("B" & i).Value = tmp(LBound(tmp))
        Range("C" & i).Value = tmp(UBound(tmp))
    End If
    Erase tmp
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Which resulted in the following input/output:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Full Name</td><td style=";">First Name</td><td style=";">Last Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">John Smith</td><td style=";">John</td><td style=";">Smith</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Jane Doe</td><td style=";">Jane</td><td style=";">Doe</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">John F. Kennedy</td><td style=";">John</td><td style=";">Kennedy</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">John Jacob Jingelheimer Schmidt</td><td style=";">John</td><td style=";">Schmidt</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

 

[ravi4ever]

Hi MrKowz, your code working fine but now I am facing another little problem..
The data I am analyzing is from Linkedin and I get the names in this format.. and there's an extra space at the end of each name..

2. Shakera Scott, M.A.H.R.
8. Mary Anne Pilgrim
9. Ryan Wilhite
14. Jan Curry
20. Kim Carlson
21. Leigh Ann Paull
22. Dana Duvall

 

[MrKowz]

Try:

Public Sub GetFirstLastName()
Dim LastRow As Long, _
    i       As Long, _
    tmp     As String, _
    CommaDelim  As Variant, _
    SpaceDelim  As Variant

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    If Cells(i, 1).Value <> "" Then
        tmp = Trim$(Range("A" & i).Value)
        CommaDelim = Split(tmp, ",")
        SpaceDelim = Split(CommaDelim(LBound(CommaDelim)), " ")
        Range("B" & i).Value = SpaceDelim(LBound(SpaceDelim))
        Range("C" & i).Value = SpaceDelim(UBound(SpaceDelim))
    End If
    Erase CommaDelim
    Erase SpaceDelim
    tmp = ""
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Full Name</td><td style=";">First Name</td><td style=";">Last Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Shakera Scott, M.A.H.R. </td><td style=";">Shakera</td><td style=";">Scott</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Mary Anne Pilgrim </td><td style=";">Mary</td><td style=";">Pilgrim</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Ryan Wilhite </td><td style=";">Ryan</td><td style=";">Wilhite</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Jan Curry </td><td style=";">Jan</td><td style=";">Curry</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Kim Carlson </td><td style=";">Kim</td><td style=";">Carlson</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Leigh Ann Paull </td><td style=";">Leigh</td><td style=";">Paull</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Dana Duvall </td><td style=";">Dana</td><td style=";">Duvall</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />