VBA Parsing and Editing complex human names

[ABQDave]

I have several lists of more than 1000 names entered as a single column in Excel 2010
I need to split each string into fields such as FName, LName, MName, Title, Suffix
sample data:
[TABLE="width: 173"]
<tbody>[TR]
[TD]Dr. Bill Lovejoy[/TD]
[/TR]
[TR]
[TD]Dr. Raymond Lutz, Jr.[/TD]
[/TR]
[TR]
[TD]Dr. Nancy Lutz[/TD]
[/TR]
[TR]
[TD]William Macey[/TD]
[/TR]
[TR]
[TD]Paul and Denise Maestas[/TD]
[/TR]
[TR]
[TD](and many other variants)
[/TD]
[/TR]
</tbody>[/TABLE]
I've written a VB program that handles most of the cases and
identifies FName, LName, MName, Title, Suffix reading one string at a time. see commented out strTest=

Now I need to modify this to read the string strText from column A and write the five fields above in col B-F
Notice that StrTest is redefined in the code after I've handled the title and suffix in order to make a simpler string for the next step.

I don't known how to set the data types and loop through the spreadsheet to read the data. I attempted to set strTest as variant, but it stops when I redefine it. Setting it as range as I've tried here doesn't work.

Thanks,

ABQDave

Private Sub cleanNames()
Dim strTest As Range

Dim Fname As String
Dim Lname As String
Dim Mname As String
Dim WifeName As String
Dim Title As String
Dim Suffix As String

Dim strArray() As String
Dim intCount As Integer
Dim intSpaces As Integer
Dim intLen As Integer
Dim intComma As Integer


'strTest = "J. Douglas Mistler, USA (Ret.)"

For Each strTest In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)



strTest = Trim(strTest)
intLen = Len(strTest)
intComma = InStr(strTest, ",")

'handle suffix--anything after a comma
Suffix = Right(strTest, (intLen - intComma))
strTest = Left(strTest, intComma - 1)
intLen = Len(strTest)
Debug.Print intComma, Suffix, strTest1
'deal with titles

etc

 

[hiker95]

ABQDave,

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better, in this case:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ABQDave]

My program is in Excel 2010 VB. The workbook is available to public at
https://drive.google.com/folderview?id=0B1PMPIUClDfINEU2T3U5U1puQWs&usp=sharing
I don't have Box account. If you prefer, I can add screenshots. Thanks,

ABQDave

I have several lists of more than 1000 names entered as a single column in Excel 2010
I need to split each string into fields such as FName, LName, MName, Title, Suffix
sample data:
[TABLE="width: 173"]
<tbody>[TR]
[TD]Dr. Bill Lovejoy[/TD]
[/TR]
[TR]
[TD]Dr. Raymond Lutz, Jr.[/TD]
[/TR]
[TR]
[TD]Dr. Nancy Lutz[/TD]
[/TR]
[TR]
[TD]William Macey[/TD]
[/TR]
[TR]
[TD]Paul and Denise Maestas[/TD]
[/TR]
[TR]
[TD](and many other variants)[/TD]
[/TR]
</tbody>[/TABLE]
I've written a VB program that handles most of the cases and
identifies FName, LName, MName, Title, Suffix reading one string at a time. see commented out strTest=

Now I need to modify this to read the string strText from column A and write the five fields above in col B-F
Notice that StrTest is redefined in the code after I've handled the title and suffix in order to make a simpler string for the next step.

I don't known how to set the data types and loop through the spreadsheet to read the data. I attempted to set strTest as variant, but it stops when I redefine it. Setting it as range as I've tried here doesn't work.

Thanks,

ABQDave

Private Sub cleanNames()
Dim strTest As Range

Dim Fname As String
Dim Lname As String
Dim Mname As String
Dim WifeName As String
Dim Title As String
Dim Suffix As String

Dim strArray() As String
Dim intCount As Integer
Dim intSpaces As Integer
Dim intLen As Integer
Dim intComma As Integer


'strTest = "J. Douglas Mistler, USA (Ret.)"

For Each strTest In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)



strTest = Trim(strTest)
intLen = Len(strTest)
intComma = InStr(strTest, ",")

'handle suffix--anything after a comma
Suffix = Right(strTest, (intLen - intComma))
strTest = Left(strTest, intComma - 1)
intLen = Len(strTest)
Debug.Print intComma, Suffix, strTest1
'deal with titles

etc

 

[hiker95]

ABQDave,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thanks for the workbook.

I did also ask for post a screenshot of the worksheet results (manually formatted by you) that you are looking for.

Here is a screenshot of the raw data for anyone else that may be able to help you.

Excel 2007
	A	B	C	D	E	F	G
1	FullName	Lname	Fname	Mname	WifeName	Title	Suffix
2	Frank Morgan, III and
3	Patricia Morgan
4	Julia morgan*
5	H. Hunter and M. Ann Look
6	Dr. Bill Killjoy
7	Dr. Raymond Klutz, Jr. and
8	Dr. Nancy Klutz
9	William Macey
10	Paul and Denise Masters
11	Robert Mellors and
12	Dr. Gloria Mellors
13	Charles and
14	Margery Herschell
15	Felipe and Phyllis Marquez
16	Ted and Dolores Marquez
17	Dr. Valerie Matthews
18	Stanley and
19	Jane Matthews
20	C. Herman Monday
21	Karin April
22	Sharelle Append
23	Drs. Marguerite and
24	Liam* McCrain
25	Michael and
26	Barbara McConnell
27	Jeannette McKink
28	Terrell Green and
29	Dr. Lisa Conroy
30	William Landon and
31	S. Patricia Harry
32	Dr. J. T. (Skip) Richards
33	Mr. and Mrs. John Lerner
34
testdata

I have looked at your raw data, and, I can not see the logic for solving your request.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[ABQDave]

Here's a possible strategy--convert the input to a text file and split it from within VB, put name components into 2 dim array and write array back to a worksheet.
Here's a bit of code following a post on excel - Read/Parse text file line by line in VBA - Stack Overflow.
I think this will work, but there must be a way to do this without the extra step of creating a text file.

Sub readfile()
Dim hf As Integer: hf = FreeFile
Dim lines() As String
Dim i As Long
Dim xdata(33, 6) As Variant


Open "d:\350Names\TestNames.txt" For Input As #hf
lines = Split(Input$(LOF(hf), #hf), vbNewLine)
Close #hf
For i = 0 To UBound(lines)

xdata(i, 1) = lines(i)
Debug.Print "xdata"; i; "="; xdata(i, 1)
Next
Range("a1:f33") = xdata
End Sub