VBA macro extracting wrong data

[johnmerlino]

Hey all,

If I have a pattern like this in column A (first name last name ampersand first name last name):


HORACIO ABREU & KARYNNA MARTINEZ


I am trying to get this:

ABREU

In other words, I want the last name on person on left.

This is what I have:

Sub GetName()

Dim lngLastRow As Long
Dim i

lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row



For i = 1 To lngLastRow

pos = InStr(Range("A" & i), " & ")

main_name = Mid(Range("A" & i).Value, 1, pos - 1)

'Value = Mid(Range("A" & i).Value, InStrRev(Trim(main_name), " ") + 1, pos - 4)



Next i



End Sub

The above macro outputs this:

ABREU & KA

thanks for response

 

[Jonmo1]

Try using Split

Sub GetName()
Dim lngLastRow As Long
Dim i As Long
Dim MyVals As Variant, Lname As String
lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lngLastRow
    MyVals = Split(Cells(i, "A"), " & ")
    MyVals = Split(MyVals(LBound(MyVals)))
    Lname = MyVals(UBound(MyVals))
Next i
End Sub

 

[p45cal]

try:

 Value = Mid(Trim(main_name), InStrRev(Trim(main_name), " ") + 1, Len(main_name))

or

Value = Split(Application.Trim(Split(Range("A" & i).Value, "&")(0)))(1)

 

[Akinrotimi]

If you have the following on A2:

<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 204pt; mso-width-source: userset; mso-width-alt: 7736" width=272><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 204pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=272>HORACIO ABREU & KARYNNA MARTINEZ

Then place the code below on cell A3:

</TD></TR></TBODY></TABLE>
=MID(LEFT(A2,FIND("&",A2)-2),FIND(" ",LEFT(A2,FIND("&",A2)-2),1)+1,LEN(LEFT(A2,FIND("&",A2)-2)))

This works perfectly.

Regards

Rotimi