Simple parsing of fullname to one segment

[jsb111]

I have not programmed vb in years and I am having dificulty in getting this function to return the string segment requested into the excel cell.
if cell A1 = Mr John Jacob Gingleheimer Smith DDS
= Parsename (A1,5) in cell B1 should return Smith.

But this does work for me. Any suggestions?



Public Function ParseName(FullName As String, SegN As Integer) As String
'Parsename brings back a segment of name (1-? segments)
Dim counter As Integer, begseg As Integer, endseg As Integer

While counter < SegN
counter = counter + 1
begseg = endseg
endseg = InStr(begseg, FullName, " ")
Wend

ParseName = Mid(FullName, begseg, endseg - begseg)

End Function

 

[Datsmart]

This formula will gather the 5th name from a text string in cell A1.

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("@",SUBSTITUTE(A1," ","@",4)),"")," ",REPT(" ",100),1),100))

 

[jsb111]

John
Thanks for your reply.

I was hoping to write a UDF that I could use over and over to parse a column of variating names. I would use Excel's autofilter to select and correct the segment numbers for Fname, Mname, Lname, prefix, & suffix.

 

[jsb111]

This is still a little buggy but it sort of does what I want:


Public Function ParseName(FullName As String, SegN As Integer, del As String) As String
'Parsename brings back a segment of fullname (1-? segments)
Dim counter As Integer, begseg As Integer, endseg As Integer

Do While counter < SegN
counter = counter + 1
begseg = endseg
endseg = InStr(begseg + 1, FullName, del)
If endseg = 0 Then Exit Do

Loop

If counter = SegN Then ' found segment
If endseg > begseg Then
ParseName = Mid(FullName, begseg + 1, endseg - begseg)
Else
ParseName = Mid(FullName, begseg + 1)
End If

Else
ParseName = "NA"

End If

End Function

 

[Richard Schollar]

hI

Assuming you are using xl2000 or above then maybe:

Function ParseString(s As String,lSeg As Long) As String
Dim vArr As Variant
vArr = Split(s, " ")
ParseString = vArr(lSeg-1)
End Function