# Simple parsing of fullname to one segment

#### jsb111

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
This formula will gather the 5th name from a text string in cell A1.
Code:
``=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("@",SUBSTITUTE(A1," ","@",4)),"")," ",REPT(" ",100),1),100))``

John

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.

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

hI

Assuming you are using xl2000 or above then maybe:

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

Replies
2
Views
4K
Replies
5
Views
583
Replies
4
Views
787
Replies
0
Views
907
Replies
3
Views
1K

1,216,131
Messages
6,129,066
Members
449,485
Latest member
greggy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back