Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

"Find" in VB

Posted by mcarter973 on November 14, 2001 11:46 AM

I'm trying to extract the last name from a full name (including middle initial). I think the formula should start from the right and find the first space.

Is there a variable in VB that is similar to the "Find" function in Excel?

Thanks.

Check out our Excel VBA Resources

Try with InStr or...

Posted by Juan Pablo on November 14, 2001 12:15 PM

You could use the Split function...

for example

Sub SplitString()
Dim x As Variant
x = Split("Juan Pablo González", " ")
For i = 0 To UBound(x)
Debug.Print x(i)
Next i
End Sub

Will produce:
Juan
Pablo
González

You can use n(0) to find first name, n(UBound(n)) to get last name, or anything like that.

Juan Pablo

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.