Simple parsing of fullname to one segment

jsb111

New Member
Joined
Jul 26, 2007
Messages
3
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))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top