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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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))
 

jsb111

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

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top