By Patrick Matthews
帕特里克·马修斯
1.简介 (1. Introduction)
Any time you have to store or process people's names, best practice dictates that you maintain each "name part", e.g., title, first name, middle name, last/surname, and/or suffix, as a discrete piece of information:
每次您必须存储或处理人员的姓名时，最佳实践都要求您维护每个“名称部分”，例如标题，名字，中间名，姓氏/姓氏和/或后缀，作为离散的信息：
In a data entry/edit form, there should be different controls for each name part
在数据输入/编辑表单中，每个名称部分应有不同的控件
In an Excel list, each name part should be stored in a different column
在Excel列表中，每个名称部分应存储在不同的列中
In a database table, each name part should be stored in a different column
在数据库表中，每个名称部分应存储在不同的列中
In a VB6/VBA procedure, each name part should be stored in a different variable (or as different elements in an array, collection, or similar object)
在VB6 / VBA过程中，每个名称部分应存储在不同的变量中（或存储在数组，集合或类似对象中的不同元素中）
Doing so enables fast and efficient searches, and also enables atomicity.
这样做可以实现快速有效的搜索，还可以实现原子性 。
However, you may have to work with a data set that did not adhere to these best practice guidelines, and thus you may be struggling with parsing the resulting "full name" strings to extract the various name parts.
但是，您可能必须使用不符合这些最佳实践准则的数据集，因此，您可能在解析结果“全名”字符串以提取各种名称部分时费劲。
This article will demonstrate some of the challenges inherent to parsing peoples names, provide a look at some techniques for parsing simple names in Excel, Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA).
本文将演示解析人名所固有的一些挑战，并介绍一些用于解析Excel，Access，Visual Basic 6（VB6）和Visual Basic for Applications（VBA）中的简单名称的技术。
This article also includes the source code for two user defined functions, GetNamePart and GetAllNameParts, that you may use to parse peoples name, providing greater flexibility and ease of use than is possible using only native functions.
本文还包括两个用户定义函数GetNamePart和GetAllNameParts的源代码，您可以使用它们来解析人员姓名，与仅使用本机函数相比，它们提供了更大的灵活性和易用性。
With apologies to users from other cultures, this article will focus on names as they typically occur in English-speaking cultures.
在向其他文化的用户致歉时，本文将重点介绍英语国家/地区通常使用的名称。
2.名称是什么？ 为什么名称解析如此困难 (2. What's in a Name? Why Name Parsing Is So Difficult)
A very commonly asked question type on Experts Exchange involves parsing a "full name" string, and extracting from it the "name parts" such as title/honorific, first name, middle name, last name/surname, and suffix.
在Experts Exchange上，一个非常常见的问题类型涉及解析“全名”字符串，并从中提取“名称部分”，例如标题/名誉，名字，中间名，姓氏/姓氏和后缀。
The following question makes an excellent example:
以下问题是一个很好的例子：
Using an access 2007 database. There is a field 'contact' that has hundreds of names in it. There are many formats:Mr. FirstName LastName
Mrs. FirstName LastName
FirstName LastName
FirstName LastName-LastName2
FirstName LastName LastName2 Jr/Sr
I want to automatically get them into 2 columns: firstname, lastname
First, I am going to strip out ALL Mr., Mrs., Ms. to make it simple. So I know the first token is FirstName and the REST is LastName.
How can I accomplish this?
名姓先生
姓氏夫人
名姓
名字姓氏-姓氏2
FirstName LastName LastName2 Jr / Sr
我想自动将它们分为2列：名字，姓氏
首先，我将剔除所有的先生，太太，女士，以使其变得简单。 所以我知道第一个令牌是名字，而REST是姓氏。
我该怎么做？
Similar questions ask for name parsing, but using a slightly different structure:
类似的问题要求名称解析，但使用的结构略有不同：
I have a column in Excel that shows a full name, in the following format:
Lastname, Firstname Middlename
Some entries may have titles and/or suffixes as well:
Lastname <Suffix>, <Title> Firstname Middlename
Please help me extract the Title, First Name, Middle Name, Last Name, and Suffix into separate columns.
姓，名中间名
一些条目可能还具有标题和/或后缀：
姓<后缀>，<标题>姓中间名
请帮助我将标题，名字，中间名，姓氏和后缀提取到单独的列中。
In either case, while the question is seemingly simple and straightforward, as the next section will show, name parsing can be a very complex matter.
无论哪种情况，问题似乎很简单明了，正如下一节将要显示的那样，名称解析可能是一件非常复杂的事情。
The most common data entry formats for personal names are as follows:
个人姓名最常见的数据输入格式如下：
In either case, typically the first name and last name are both present, while the title, middle name, and/or suffixes are optional.
在这两种情况下，通常都存在名字和姓氏，而标题，中间名和/或后缀是可选的。
Of course, each name part can itself be somewhat complex:
当然，每个名称部分本身可能都有些复杂：
Thus, for each name part:
因此，对于每个名称部分：
The name part may or may not appear, as in the case of titles, middle names, and suffixes
名称部分可能会出现，也可能不会出现，例如标题，中间名和后缀
The name part may be a single word or be compound (more than one word, as in "Joe Bob" in "Joe Bob Briggs", "Ronald Reuel" in "John Ronald Reuel Tolkien", or "de la Renta" in "Oscar de la Renta"). In addition, there may be more than one title and/or suffix in a single full name
名称部分可以是单个单词，也可以是复合单词（例如，“ Joe Bob Briggs”的“ Joe Bob”，“ John Ronald Reuel Tolkien”的“ Ronald Reuel”或“ de la Renta”中的多个单词）奥斯卡·德拉伦塔（Oscar de la Renta）”。 此外，一个全名可能包含多个标题和/或后缀
Name parts may be abbreviated, sometimes but always with a period
名称部分可以缩写，有时但总是带有句点
Name parts may be hyphenated
名称部分可能带有连字符
Taken together, these contingencies can make name parsing a difficult challenge indeed.
综上所述，这些意外事件确实会使名称解析成为一个困难的挑战。
3.使用本机Excel，Access和VB6 / VBA函数解析名称 (3. Parsing a Name Using Native Excel, Access, and VB6/VBA Functions)
Consider relatively simple full names, as in the examples below. In these cases, the parsing task is very easy:
考虑相对简单的全名，如以下示例所示。 在这些情况下，解析任务非常简单：
Full Name: John Smith
Assumption: Space delimits first and last names
Excel (full name in A1)
------------------------------------------
First Name: =LEFT(A1,FIND(" ",A1)-1)
Last Name: =MID(A1,FIND(" ",A1)+1,LEN(A1))
Access (full name in column FullName)
------------------------------------------
First Name: Left([FullName], InStr(1, [FullName], " ") - 1)
Last Name: Mid([FullName], InStr(1, [FullName], " ") + 1)
VB6/VBA (full name in variable FullName)
------------------------------------------
First Name: Left(FullName, InStr(1, FullName, " ") - 1)
Last Name: Mid(FullName, InStr(1, FullName, " ") + 1)
Full Name: Smith, John
Assumption: Comma delimits last and first names
Excel (full name in A1)
------------------------------------------
First Name: =TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))
Last Name: =LEFT(A1,FIND(",",A1)-1)
Access (full name in column FullName)
------------------------------------------
First Name: =Trim(Mid([FullName], InStr(1, [FullName], ",") + 1))
Last Name: =Left([FullName], InStr(1, [FullName], ",") - 1)
VB6/VBA (full name in variable FullName)
------------------------------------------
First Name: =Trim(Mid(FullName, InStr(1, FullName, ",") + 1))
Last Name: =Left(FullName, InStr(1, FullName, ",") - 1)
Now consider a slightly more complex situation, but probably a more typical one, in which a middle name/initial may or may not be present:
现在考虑稍微复杂一点的情况，但可能是更典型的情况，其中可能存在或可能不存在中间名/首字母：
Full Name: John James Smith
Assumption: Middle name may or may not be present
Excel (full name in A1)
------------------------------------------
First: =LEFT(A1,FIND(" ",A1)-1)
Middle: =IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)
+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),
"",MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),
FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))
Last: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))
Access (full name in column FullName)
------------------------------------------
First: Left([FullName], InStr(1, [FullName], " ") - 1)
Middle: IIf((Len(Replace([FullName], " ", "") - Len([FullName])) > 1,
Mid([FullName], InStr(1, [FullName], " ") + 1,
InStrRev([FullName], " ") - InStr(1, FullName, ",") - 1, "")
Last: Mid([FullName], InStrRev([FullName], " ") + 1)
VB6/VBA (full name in variable FullName)
------------------------------------------
First: Left(FullName, InStr(1, FullName, " ") - 1)
Middle: IIf((Len(Replace(FullName, " ", "") - Len(FullName)) > 1,
Mid(FullName, InStr(1, FullName, " ") + 1,
InStrRev(FullName, " ") - InStr(1, FullName, ",") - 1, "")
Last: Mid(FullName, InStrRev(FullName, " ") + 1)
The Excel expressions become incomprehensible to most users. The Access and VB6/VBA expressions are not quite as complex, but they are already trending upward in complexity. We have not even considered any cases yet with titles and/or suffixes; the expressions needed to deal with names that leave the title, middle name, and/or suffixes optional (and potentially also to deal with compound titles and suffixes) would be excruciating in terms of length and difficulty to read and understand. Add in requirements for gracefully handling consecutive spaces, trimming extraneous spaces from the name parts, and allowing for optional spaces following a comma, and the task becomes close to impossible.
大多数用户都无法理解Excel表达式。 Access和VB6 / VBA表达式并不那么复杂，但是它们的复杂性已经呈上升趋势。 我们甚至还没有考虑过任何带有标题和/或后缀的情况； 处理长度使标题，中间名和/或后缀为可选的名称（以及可能还处理复合标题和后缀）所需的表达方式，在长度和阅读和理解方面都非常麻烦。 加上一些要求，以优雅地处理连续的空格，从名称部分修剪多余的空格，并在逗号后留出可选的空格，任务几乎变得不可能。
Excel does offer one other potential trick, the Text to Columns feature. However, on complex names, it too may lack the power to do name parsing well:
Excel确实提供了另一个潜在的技巧，即“文本到列”功能。 但是，对于复杂的名称，它也可能缺乏很好地进行名称解析的能力：
If your data set has entries with "missing" name parts, your output will have a varying number of columns, and not all name parts will be in the same column
如果您的数据集包含带有“缺少”名称部分的条目，则您的输出将具有不同数量的列，并且并非所有名称部分都在同一列中
There is no way to handle compound name parts
没有办法处理复合名称部分
The graphic below demonstrates some of the difficulties involved in using Text to Columns. Note especially how the surname shows up in the first column of the transfrmed data, as expected, but how we cannot depend on any of the other name parts to show up predictably in certain columns.
下图显示了使用“文本到列”所涉及的一些困难。 请特别注意，姓氏如何按预期方式显示在已转换数据的第一列中，但是我们如何不能依靠其他任何名称部分来按预期显示在某些列中。
Real-life data is complicated. Successful name parsing functions or expressions should be able to handle all of the following examples gracefully:
现实生活中的数据非常复杂。 成功的名称解析函数或表达式应能够正常处理以下所有示例：
First Middle Last | Last, Middle First
-----------------------------------|------------------------------
John Smith | Smith, John
John James Smith | Smith, John James
Dr. John Smith | Smith, Dr. John
Dr John James Smith III | Smith III, Dr John James
Rev. Dr. Martin Luther King, Jr. | King, Jr., Rev. Dr. Martin Luther
Elizabeth Marie Jones, M.D, Ph.D. | Jones, M.D, Ph.D., Elizabeth Marie
Elizabeth Marie Jones,MD,PhD | Jones,MD,PhD,Elizabeth Marie
Nancy Jones-Smythe | Jones-Smythe, Nancy
At this point it should be clear that relying on native functions alone will suffice only for the very simplest names we might encounter.
在这一点上很明显，仅依赖于本机函数就足以满足我们可能遇到的最简单的名称。
[b]The GetNamePart and GetAllNameParts user defined functions, described in the next section, are able to parse all of the full names above.[/b]
[b]
4. GetNamePart和GetAllNameParts用户定义的函数 (4. GetNamePart and GetAllNameParts User Defined Functions)
The GetNamePart and GetAllNameParts user defined functions are a flexible means for parsing full names to extract titles, first names, middle names, last/surnames, and/or suffixes.
GetNamePart和GetAllNameParts用户定义的函数是一种用于解析全名以提取标题，名字，中间名，姓氏/姓氏和/或后缀的灵活方法。
GetNamePart returns a specific name part from the full name string
GetNamePart返回全名字符串中的特定名称部分
GetAllNameParts returns a Dictionary object with items for all possible name parts in the input string (keys are Title, First, Middle, Last, and Suffix)
GetAllNameParts返回一个Dictionary对象，其中包含输入字符串中所有可能的名称部分的项（键为Title，First，Middle，Last和Suffix）。
GetNamePart and GetAllNameParts will work with both of the typical name orders, i.e., First Middle Last and Last, First Middle. However, you will not be able to mix the two name orders in the same data set, as the name order is one of the inputs into the functions.
GetNamePart和GetAllNameParts将与两种典型的名称顺序一起使用，即First Middle Last和Last，First Middle。 但是，您将无法在同一数据集中混用两个名称顺序，因为名称顺序是函数的输入之一。
GetNamePart and GetAllNameParts also gracefully handle compound titles and suffixes. Compound first, middle, and/or last names can still be problematic, however.
GetNamePart和GetAllNameParts还可以正常处理复合标题和后缀。 但是，复合的名字，中间名和/或姓氏仍然可能有问题。
You can use GetNamePart in any VB6/VBA project, or directly in an Excel worksheet formula or Access query. GetAllNameParts, because it returns a Dictionary object, can be used in any VB6/VBA project, but cannot be used directly in an Excel worksheet formula or Access query.
您可以在任何VB6 / VBA项目中或直接在Excel工作表公式或Access查询中使用GetNamePart。 由于GetAllNameParts返回一个Dictionary对象，因此可以在任何VB6 / VBA项目中使用，但不能直接在Excel工作表公式或Access查询中使用。
Here is the source code for GetNamePart and GetAllNameParts:
这是GetNamePart和GetAllNameParts的源代码：
Option Explicit
Function GetNamePart(NameStr As String, NamePart As String, Optional UseMiddle As Boolean = True, _
Optional FirstMiddleLast As Boolean = True)
' Function by Patrick Matthews
' This code may be freely used or distributed so long as you acknowledge authorship and cite the URL
' where you found it
' This function relies on Regular Expressions. For more information on RegExp, please see:
' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.html
' This function parses a name string, and depending on the arguments passed to it it returns a
' title, first name, middle name, surname, or suffix.
' This function can be used in any VBA or VB6 project. It can also be used directly in Excel worksheet
' formulae and in Access queries
' The function takes the following arguments:
'
' 1) NameStr is the full name to be parsed. Its assumed structure is determined by the FirstMiddleLast
' argument
' 2) NamePart indicates the portion of the name you want returned. Valid values (NOT case sensitive):
' Title: "TITLE", "HONORIFIC", "T", "H", "1"
' First: "FIRST NAME", "FIRSTNAME", "FNAME", "F NAME", "FIRST", "F", "FN", "F N", "2"
' Middle: "MIDDLE NAME", "MIDDLENAME", "MNAME", "M NAME", "MIDDLE", "M", "MN", "M N", "3"
' Last: "LAST NAME", "LASTNAME", "LNAME", "L NAME", "LAST", "L", "LN", "L N", "SURNAME", "4"
' Suffix: "SUFFIX", "5"
' 3) UseMiddle indicates whether or not a middle name *might* be present in the NameStr. If True or
' omitted, the function assumes that a middle name *might* be there. If False, it assumes there
' is never a middle name
' 4) FirstMiddleLast indicates the order of the name parts. If True or omitted, the function assumes:
' <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>
' If False, the function assumes:
' <Surname> <Suffix (optional)>, <First name> <Middle Name (optional)>
' Notes:
' 1) The function has a prebuilt list of Titles (see GenerateLists function below), which you can modify to
' fit your needs. The Titles string will be embedded in a RegExp Pattern string, so be sure to follow
' proper RegExp Pattern syntax
' 2) The function will recognize compound titles, as long as they are delimited by spaces
' 3) The function has a prebuilt list of Suffixes (see GenerateLists function below), which you can modify to
' fit your needs. The Suffixes string will be embedded in a RegExp Pattern string, so be sure to
' follow proper RegExp Pattern syntax
' 4) The function will recognize compound suffixes, as long as they are delimited by commas and/or spaces
' 5) If you are using True (or omitting) for FirstMiddleLast:
' a) It is always assumed that the first name has a single "word"
' b) It is always assumed that the middle name, if present, has a single "word"
' c) After the function has identfied the title, first name, middle name, and suffix, it assumes that
' whatever is left must be the surname/last name
' d) Thus, this function will process compound first/middle names incorrectly
' 6) If you are using False for FirstMiddleLast:
' a) It is always assumed that the last comma in NameStr delimits the <Surname><Suffix> block
' from the <Title><First name><Middle name> block
' b) Whatever is left in the <Surname><Suffix> block after the suffix has been removed is assumed
' to be the last name
' c) After the Title is removed from the <Title><First name><Middle name> block, if there is only
' one "word", it is the first name. If there are 2+ "words" and UseMiddle = True or omitted,
' then the last word is the middle name, and the rest is the first name
' d) Thus, this function will process compound middle names incorrectly, and may erroneously think
' a compound first name is a first name and a middle name
Dim Title As String
Dim FName As String
Dim MName As String
Dim LName As String
Dim Suffix As String
Dim RegXReturn As Object
Dim NameArr As Variant
Dim Counter As Long
Dim StartsAt As Long
Dim TitleLen As Long
Dim LastComma As Long
Dim Part1 As String
Dim Part2 As String
Static Titles As String
Static Suffixes As String
Static RegX As Object 'holding as a Static variable to improve performance
If Trim(NameStr) = "" Or Trim(NamePart) = "" Then
GetNamePart = ""
Exit Function
End If
If Titles = "" Then Titles = GenerateLists("Titles")
If Suffixes = "" Then Suffixes = GenerateLists("Suffixes")
' remove leading and trailing spaces
NameStr = Trim(NameStr)
' instantiate RegExp if needed (static variable, so it will remain in between calls)
If RegX Is Nothing Then
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.IgnoreCase = True ' case insensitive
.Global = True ' finds all matches, not just first match
End With
End If
' Determine structure of NameStr
If FirstMiddleLast Then
' NameStr is structured <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>
' Set Pattern to look for titles at beginning of NameStr
RegX.Pattern = "^(" & Titles & ")\.? +"
' Look for titles. Use Do...Loop to allow for compound suffixes, as in "Rev. Mr. Arthur Dimmesdale"
Do
Set RegXReturn = RegX.Execute(Mid(NameStr, TitleLen + 1))
If RegXReturn.Count > 0 Then
TitleLen = TitleLen + Len(RegXReturn(0))
Else
Exit Do
End If
Loop
' Get Title
Title = Trim(Left(NameStr, TitleLen))
' Adjust NameStr
NameStr = Mid(NameStr, TitleLen + 1)
' Look for suffixes. Use Do...Loop to allow for compound suffixes, as in "Alfred E. Neumann, PhD, Esq."
' StartsAt indicates where the suffix(es) start in the NameStr. Initialize to -1, indicating no suffix
StartsAt = -1
' Set Pattern to look for suffix at end of NameStr
RegX.Pattern = "(, *| +)(" & Suffixes & ")\.?$"
' Evaluate the NameStr. As long as a suffix is found in the portion evaluated, reset the StartsAt variable.
' When no more suffixes are found, terminate the loop
Do
Set RegXReturn = RegX.Execute(Left(NameStr, IIf(StartsAt > -1, StartsAt, Len(NameStr))))
If RegXReturn.Count > 0 Then
StartsAt = RegXReturn(0).FirstIndex ' Recall that FirstIndex starts at position zero, not 1 !!!
Else
Exit Do
End If
Loop
' If a suffix is found, then grab the entire suffix
If StartsAt > -1 Then
Suffix = Mid(NameStr, StartsAt + 1)
' remove comma if applicable
If Left(Suffix, 1) = "," Then Suffix = Mid(Suffix, 2)
' remove possible leading space
Suffix = Trim(Suffix)
' adjust NameStr to remove suffixes
NameStr = Left(NameStr, StartsAt)
End If
' Ensure we have space delimiters for remaining NameStr
NameStr = Replace(NameStr, ".", ". ")
' Remove extraneous spaces
RegX.Pattern = " {2,}"
NameStr = Trim(RegX.Replace(NameStr, " "))
' Create zero-based array with remaining "words" in the name
NameArr = Split(NameStr, " ")
' First name is always assumed to be in position zero
FName = NameArr(0)
' Depending on how many "words" are left and whether middle name is assumed to possibly be there
' (UseMiddle argument), grab middle/last names
Select Case UBound(NameArr)
Case 0
'no middle or last names
Case 1
' first name and last name
LName = NameArr(1)
Case Else
' potentially first, middle, and last names are present
If UseMiddle Then
' position 1 is assumed to be middle name, and positions 2 to N the last name
MName = NameArr(1)
' remaining "words" are for last name
For Counter = 2 To UBound(NameArr)
LName = LName & " " & NameArr(Counter)
Next
' drop leading space
LName = Trim(LName)
Else
' assume no middle name, and all remaining words are for the last name
For Counter = 1 To UBound(NameArr)
LName = LName & " " & NameArr(Counter)
Next
' drop leading space
LName = Trim(LName)
End If
End Select
Else
' NameStr is structured <Surname> <Suffix (optional)>, <Title (optional)> <First name> <Middle Name (optional)>
' Find position of last comma
LastComma = InStrRev(NameStr, ",")
If LastComma > 0 Then
' Part1 will be <Surname> <Suffix (optional)> block;
' Part2 is <Title (optional)> <First name> <Middle Name (optional)>
Part1 = Trim(Left(NameStr, LastComma - 1))
Part2 = Trim(Mid(NameStr, LastComma + 1))
' Look for suffixes. Use Do...Loop to allow for compound suffixes, as in "Neumann, PhD, Esq., Alfred E."
' StartsAt indicates where the suffix(es) start in Part1. Initialize to -1, indicating no suffix
StartsAt = -1
' Set Pattern to look for suffix at end of Part1
RegX.Pattern = "(, *| +)(" & Suffixes & ")\.?$"
' Evaluate Part1. As long as a suffix is found in the portion evaluated, reset the StartsAt variable.
' When no more suffixes are found, terminate the loop
Do
Set RegXReturn = RegX.Execute(Left(Part1, IIf(StartsAt > -1, StartsAt, Len(Part1))))
If RegXReturn.Count > 0 Then
StartsAt = RegXReturn(0).FirstIndex ' Recall that FirstIndex starts at position zero, not 1 !!!
Else
Exit Do
End If
Loop
' If a suffix is found, then grab the entire suffix
If StartsAt > -1 Then
Suffix = Mid(Part1, StartsAt + 1)
' remove comma if applicable
If Left(Suffix, 1) = "," Then Suffix = Mid(Suffix, 2)
' remove possible leading space
Suffix = Trim(Suffix)
' adjust Part1 to remove suffixes
Part1 = Left(Part1, StartsAt)
End If
LName = Trim(Part1)
' Set Pattern to look for titles at beginning of Part2
RegX.Pattern = "^(" & Titles & ")\.? +"
' Look for titles. Use Do...Loop to allow for compound suffixes, as in "Dimmesdale, Rev. Mr. Arthur"
Do
Set RegXReturn = RegX.Execute(Mid(Part2, TitleLen + 1))
If RegXReturn.Count > 0 Then
TitleLen = TitleLen + Len(RegXReturn(0))
Else
Exit Do
End If
Loop
' Get Title
Title = Trim(Left(Part2, TitleLen))
' Adjust Part2
Part2 = Mid(Part2, TitleLen + 1)
' Ensure we have space delimiters for remaining Part2
Part2 = Replace(Part2, ".", ". ")
' Remove extraneous spaces
RegX.Pattern = " {2,}"
Part2 = Trim(RegX.Replace(Part2, " "))
' Grab first/middle names from Part2
If UseMiddle And InStr(1, Part2, " ") > 0 Then
MName = Mid(Part2, InStrRev(Part2, " ") + 1)
FName = Left(Part2, InStrRev(Part2, " ") - 1)
Else
FName = Part2
End If
End If
End If
' determine function's return value
Select Case UCase(NamePart)
Case "TITLE", "HONORIFIC", "T", "H", "1"
GetNamePart = Title
Case "FIRST NAME", "FIRSTNAME", "FNAME", "F NAME", "FIRST", "F", "FN", "F N", "2"
GetNamePart = FName
Case "MIDDLE NAME", "MIDDLENAME", "MNAME", "M NAME", "MIDDLE", "M", "MN", "M N", "3"
GetNamePart = MName
Case "LAST NAME", "LASTNAME", "LNAME", "L NAME", "LAST", "L", "LN", "L N", "SURNAME", "4"
GetNamePart = LName
Case "SUFFIX", "S", "5"
GetNamePart = Suffix
Case Else
GetNamePart = ""
End Select
' destroy object variable
Set RegXReturn = Nothing
End Function
Function GetAllNameParts(NameStr As String, Optional UseMiddle As Boolean = True, _
Optional FirstMiddleLast As Boolean = True)
' Function by Patrick Matthews
' This code may be freely used or distributed so long as you acknowledge authorship and cite the URL
' where you found it
' This function relies on Regular Expressions. For more information on RegExp, please see:
' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.html
' This function parses a name string, and returns a Dictionary object (Microsoft Scripting Runtime) with
' keys corresponding to title, first name, middle name, surname, and suffix. If a name part is missing from the
' full name, the Dictionary item associated with that key is a zero-length string. The keys are:
' Title, First, Middle, Last, and Suffix (not case sensitive)
' This function can be used in any VBA or VB6 project. However, it cannot be used directly in an Excel
' worksheet formula or an Access query
' The function takes the following arguments:
'
' 1) NameStr is the full name to be parsed. Its assumed structure is determined by the FirstMiddleLast
' argument
' 2) UseMiddle indicates whether or not a middle name *might* be present in the NameStr. If True or
' omitted, the function assumes that a middle name *might* be there. If False, it assumes there
' is never a middle name
' 3) FirstMiddleLast indicates the order of the name parts. If True or omitted, the function assumes:
' <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>
' If False, the function assumes:
' <Surname> <Suffix (optional)>, <First name> <Middle Name (optional)>
' Notes:
' 1) The function has a prebuilt list of Titles (see GenerateLists function below), which you can modify to
' fit your needs. The Titles string will be embedded in a RegExp Pattern string, so be sure to follow
' proper RegExp Pattern syntax
' 2) The function will recognize compound titles, as long as they are delimited by spaces
' 3) The function has a prebuilt list of Suffixes (see GenerateLists function below), which you can modify to
' fit your needs. The Suffixes string will be embedded in a RegExp Pattern string, so be sure to
' follow proper RegExp Pattern syntax
' 4) The function will recognize compound suffixes, as long as they are delimited by commas and/or spaces
' 5) If you are using True (or omitting) for FirstMiddleLast:
' a) It is always assumed that the first name has a single "word"
' b) It is always assumed that the middle name, if present, has a single "word"
' c) After the function has identfied the title, first name, middle name, and suffix, it assumes that
' whatever is left must be the surname/last name
' d) Thus, this function will process compound first/middle names incorrectly
' 6) If you are using False for FirstMiddleLast:
' a) It is always assumed that the last comma in NameStr delimits the <Surname><Suffix> block
' from the <Title><First name><Middle name> block
' b) Whatever is left in the <Surname><Suffix> block after the suffix has been removed is assumed
' to be the last name
' c) After the Title is removed from the <Title><First name><Middle name> block, if there is only
' one "word", it is the first name. If there are 2+ "words" and UseMiddle = True or omitted,
' then the last word is the middle name, and the rest is the first name
' d) Thus, this function will process compound middle names incorrectly, and may erroneously think
' a compound first name is a first name and a middle name
Dim Title As String
Dim FName As String
Dim MName As String
Dim LName As String
Dim Suffix As String
Dim RegXReturn As Object
Dim NameArr As Variant
Dim Counter As Long
Dim StartsAt As Long
Dim TitleLen As Long
Dim LastComma As Long
Dim Part1 As String
Dim Part2 As String
Dim dic As Object
Static Titles As String
Static Suffixes As String
Static RegX As Object 'holding as a Static variable to improve performance
If Titles = "" Then Titles = GenerateLists("Titles")
If Suffixes = "" Then Suffixes = GenerateLists("Suffixes")
Set dic = CreateObject("Scripting.Dictionary")
With dic
.CompareMode = 1
.Add "Title", ""
.Add "First", ""
.Add "Middle", ""
.Add "Last", ""
.Add "Suffix", ""
End With
If Trim(NameStr) = "" Then
Set GetAllNameParts = dic
Set dic = Nothing
Exit Function
End If
' remove leading and trailing spaces
NameStr = Trim(NameStr)
' instantiate RegExp if needed (static variable, so it will remain in between calls)
If RegX Is Nothing Then
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.IgnoreCase = True ' case insensitive
.Global = True ' finds all matches, not just first match
End With
End If
' Determine structure of NameStr
If FirstMiddleLast Then
' NameStr is structured <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>
' Set Pattern to look for titles at beginning of NameStr
RegX.Pattern = "^(" & Titles & ")\.? +"
' Look for titles. Use Do...Loop to allow for compound suffixes, as in "Rev. Mr. Arthur Dimmesdale"
Do
Set RegXReturn = RegX.Execute(Mid(NameStr, TitleLen + 1))
If RegXReturn.Count > 0 Then
TitleLen = TitleLen + Len(RegXReturn(0))
Else
Exit Do
End If
Loop
' Get Title
Title = Trim(Left(NameStr, TitleLen))
' Adjust NameStr
NameStr = Mid(NameStr, TitleLen + 1)
' Look for suffixes. Use Do...Loop to allow for compound suffixes, as in "Alfred E. Neumann, PhD, Esq."
' StartsAt indicates where the suffix(es) start in the NameStr. Initialize to -1, indicating no suffix
StartsAt = -1
' Set Pattern to look for suffix at end of NameStr
RegX.Pattern = "(, *| +)(" & Suffixes & ")\.?$"
' Evaluate the NameStr. As long as a suffix is found in the portion evaluated, reset the StartsAt variable.
' When no more suffixes are found, terminate the loop
Do
Set RegXReturn = RegX.Execute(Left(NameStr, IIf(StartsAt > -1, StartsAt, Len(NameStr))))
If RegXReturn.Count > 0 Then
StartsAt = RegXReturn(0).FirstIndex ' Recall that FirstIndex starts at position zero, not 1 !!!
Else
Exit Do
End If
Loop
' If a suffix is found, then grab the entire suffix
If StartsAt > -1 Then
Suffix = Mid(NameStr, StartsAt + 1)
' remove comma if applicable
If Left(Suffix, 1) = "," Then Suffix = Mid(Suffix, 2)
' remove possible leading space
Suffix = Trim(Suffix)
' adjust NameStr to remove suffixes
NameStr = Left(NameStr, StartsAt)
End If
' Ensure we have space delimiters for remaining NameStr
NameStr = Replace(NameStr, ".", ". ")
' Remove extraneous spaces
RegX.Pattern = " {2,}"
NameStr = Trim(RegX.Replace(NameStr, " "))
' Create zero-based array with remaining "words" in the name
NameArr = Split(NameStr, " ")
' First name is always assumed to be in position zero
FName = NameArr(0)
' Depending on how many "words" are left and whether middle name is assumed to possibly be there
' (UseMiddle argument), grab middle/last names
Select Case UBound(NameArr)
Case 0
'no middle or last names
Case 1
' first name and last name
LName = NameArr(1)
Case Else
' potentially first, middle, and last names are present
If UseMiddle Then
' position 1 is assumed to be middle name, and positions 2 to N the last name
MName = NameArr(1)
' remaining "words" are for last name
For Counter = 2 To UBound(NameArr)
LName = LName & " " & NameArr(Counter)
Next
' drop leading space
LName = Trim(LName)
Else
' assume no middle name, and all remaining words are for the last name
For Counter = 1 To UBound(NameArr)
LName = LName & " " & NameArr(Counter)
Next
' drop leading space
LName = Trim(LName)
End If
End Select
Else
' NameStr is structured <Surname> <Suffix (optional)>, <Title (optional)> <First name> <Middle Name (optional)>
' Find position of last comma
LastComma = InStrRev(NameStr, ",")
If LastComma > 0 Then
' Part1 will be <Surname> <Suffix (optional)> block;
' Part2 is <Title (optional)> <First name> <Middle Name (optional)>
Part1 = Trim(Left(NameStr, LastComma - 1))
Part2 = Trim(Mid(NameStr, LastComma + 1))
' Look for suffixes. Use Do...Loop to allow for compound suffixes, as in "Neumann, PhD, Esq., Alfred E."
' StartsAt indicates where the suffix(es) start in Part1. Initialize to -1, indicating no suffix
StartsAt = -1
' Set Pattern to look for suffix at end of Part1
RegX.Pattern = "(, *| +)(" & Suffixes & ")\.?$"
' Evaluate Part1. As long as a suffix is found in the portion evaluated, reset the StartsAt variable.
' When no more suffixes are found, terminate the loop
Do
Set RegXReturn = RegX.Execute(Left(Part1, IIf(StartsAt > -1, StartsAt, Len(Part1))))
If RegXReturn.Count > 0 Then
StartsAt = RegXReturn(0).FirstIndex ' Recall that FirstIndex starts at position zero, not 1 !!!
Else
Exit Do
End If
Loop
' If a suffix is found, then grab the entire suffix
If StartsAt > -1 Then
Suffix = Mid(Part1, StartsAt + 1)
' remove comma if applicable
If Left(Suffix, 1) = "," Then Suffix = Mid(Suffix, 2)
' remove possible leading space
Suffix = Trim(Suffix)
' adjust Part1 to remove suffixes
Part1 = Left(Part1, StartsAt)
End If
LName = Trim(Part1)
' Set Pattern to look for titles at beginning of Part2
RegX.Pattern = "^(" & Titles & ")\.? +"
' Look for titles. Use Do...Loop to allow for compound suffixes, as in "Dimmesdale, Rev. Mr. Arthur"
Do
Set RegXReturn = RegX.Execute(Mid(Part2, TitleLen + 1))
If RegXReturn.Count > 0 Then
TitleLen = TitleLen + Len(RegXReturn(0))
Else
Exit Do
End If
Loop
' Get Title
Title = Trim(Left(Part2, TitleLen))
' Adjust Part2
Part2 = Mid(Part2, TitleLen + 1)
' Ensure we have space delimiters for remaining Part2
Part2 = Replace(Part2, ".", ". ")
' Remove extraneous spaces
RegX.Pattern = " {2,}"
Part2 = Trim(RegX.Replace(Part2, " "))
' Grab first/middle names from Part2
If UseMiddle And InStr(1, Part2, " ") > 0 Then
MName = Mid(Part2, InStrRev(Part2, " ") + 1)
FName = Left(Part2, InStrRev(Part2, " ") - 1)
Else
FName = Part2
End If
End If
End If
' determine function's return value
With dic
.Item("Title") = Title
.Item("First") = FName
.Item("Middle") = MName
.Item("Last") = LName
.Item("Suffix") = Suffix
End With
Set GetAllNameParts = dic
' destroy object variable
Set RegXReturn = Nothing
Set dic = Nothing
End Function
Private Function GenerateLists(ListType As String)
Dim Titles As String
Dim Suffixes As String
' In creating the master title and suffix lists, keep in mind that the strings will be passed in as part of a
' RegExp pattern, and so typical syntax rules for the VBScript implementation of RegExp will apply for things
' such as optional characters and escaping reserved characters. For example:
'
' M\.? ?D
'
' matches M, then zero or one period, then zero or one space, then D. Use the pipe character to delimit your
' entries
' If the lists get too long to keep using line continuators, then simply break them up into separate expressions:
'
' Titles = 'Dr|Doctor|Mrs|Ms|Miss|Mr|Mister|Master|'
' Titles = Titles & "Reverend|Rev|Right Reverend|Right Rev|Most Reverend|
' Titles = Titles & "Most Rev|Honorable|Honourable"
' Populate master title list. This can be expanded according to your needs. There is no need to include a
' trailing period here, as the Pattern string built later on includes an optional period at the end. In cases
' where a title may be shortened, list the longer version first. For example, list Senator before Sen.
Titles = "Dr|Doctor|Mrs|Ms|Miss|Mr|Mister|Master|Reverend|Rev|Right Reverend|Right Rev|Most Reverend|" & _
"Most Rev|Honorable|Honourable|Hon|Monsignor|Msgr|Father|Fr|Bishop|Sister|Sr|Mother Superior|Mother|" & _
"Senator|Sen|President|Pres|Vice President|V\.? ?P|Secretary|Sec|General|Gen|Lieutenant General|Lt\.? ?Gen|" & _
"Major General|Maj\.? ?Gen|Brigadier General|Brig\.? ?Gen|Colonel|Col|Lieutenant Colonel|Lt\.? ?Col|Major|" & _
"Maj|Sir|Dame|Lord|Lady|Judge|Professor|Prof"
' Populate master suffix list. This can be expanded according to your needs. There is no need to include a
' trailing period here, as the Pattern string built later on includes an optional period at the end. In cases
' where a title may be shortened, list the longer version first. For example, list Esquire before Esq. Also,
' list III before II, and II before I
Suffixes = "M\.? ?D|Ph\.? ?D|Esquire|Esq\.?|J\.? ?D|D\.? ?D|Jr|Sr|III|II|I|IV|X|IX|VIII|VII|VI|V|M\.? ?P|" & _
"M\.? ?S\.? ?W|C\.? P\.? ?A|P\.? M\.? ?P|L\.? ?P\.? ?N|R\.? ?N|A\.? ?S\.? ?E|U\.? ?S\.? ?N|" & _
"U\.? ?S\.? ?M\.? ?C|R\.? ?G\.? ?C\.? ?E|P\.? ?M\.? ?P|P\.? ?E|M\.? ?O\.? ?S|M\.? ?C\.? ?T\.? ?S|" & _
"M\.? ?C\.? ?T|M\.? ?C\.? ?S\.? ?E|M\.? ?C\.? ?S\.? ?D\.? ?|M\.? ?C\.? ?S\.? ?A|M\.? ?C\.? ?P\.? ?D|" & _
"M\.? ?C\.? ?M|M\.? ?C\.? ?L\.? ?T|M\.? ?C\.? ?I\.? ?T\.? ?P|M\.? ?C\.? ?D\.? ?S\.? ?T|" & _
"M\.? ?C\.? ?D\.? ?B\.? ?A|M\.? ?C\.? ?B\.? ?M\.? ?S\.? ?S|M\.? ?C\.? ?B\.? ?M\.? ?S\.? ?P|" & _
"M\.? ?C\.? ?A\.? ?S|M\.? ?C\.? ?A\.? ?D|M\.? ?C\.? ?A|I\.? ?T\.? ?I\.? ?L|C\.? ?R\.? ?P|C\.? ?N\.? ?E|" & _
"C\.? ?N\.? ?A|C\.? ?I\.? ?S\.? ?S\.? ?P|C\.? ?C\.? ?V\.? ?P|C\.? ?C\.? ?S\.? ?P|C\.? ?C\.? ?N\.? ?P|" & _
"C\.? ?C\.? ?I\.? ?E|C\.? ?A\.? ?P\.? ?M|S\.? ?J|O\.? ?F\.? ?M|C\.? ?N\.? ?D|M\.? ?B\.? ?A|M\.? ?S"
If ListType = "Titles" Then
GenerateLists = Titles
Else
GenerateLists = Suffixes
End If
End Function
GetNamePart takes four arguments:
GetNamePart接受四个参数：
NameStr is the full name to be parsed. Its assumed structure (i.e., name order) is determined by the FirstMiddleLast argument NameStr是要解析的全名。 它的假定结构（即名称顺序）由FirstMiddleLast参数确定
NamePart indicates the portion of the name you want returned NamePart表示要返回的名称部分
UseMiddle indicates whether or not a middle name might be present in the NameStr. If True or omitted, the function assumes that a middle name might be there. If False, it assumes there is never a middle name
FirstMiddleLast indicates the order of the name parts. If True or omitted, the function assumes: <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>. If False, the function assumes: <Surname> <Suffix (optional)>, <First name> <Middle Name (optional)> FirstMiddleLast指示名称部分的顺序。 如果为True或省略，则该函数假定：<标题（可选）> <名字> <中间名（可选）> <姓氏> <后缀（可选）>。 如果为False，则函数假定：<姓氏> <后缀（可选）>，<名字> <中间名（可选）>
Valid values for the NamePart argument are as follows, and are not case sensitive:
NamePart参数的有效值如下，并且 ：
Title: "TITLE", "HONORIFIC", "T", "H", "1"
标题：“ TITLE”，“ HONORIFIC”，“ T”，“ H”，“ 1”
First: "FIRST NAME", "FIRSTNAME", "FNAME", "F NAME", "FIRST", "F", "FN", "F N", "2"
第一个：“ FIRST NAME”，“ FIRSTNAME”，“ FNAME”，“ F NAME”，“ FIRST”，“ F”，“ FN”，“ FN”，“ 2”
Middle: "MIDDLE NAME", "MIDDLENAME", "MNAME", "M NAME", "MIDDLE", "M", "MN", "M N", "3"
中间：“ MIDDLE NAME”，“ MIDDLENAME”，“ MNAME”，“ M NAME”，“ MIDDLE”，“ M”，“ MN”，“ MN”，“ 3”
Last: "LAST NAME", "LASTNAME", "LNAME", "L NAME", "LAST", "L", "LN", "L N", "SURNAME", "4"
姓氏：“ LAST NAME”，“ LASTNAME”，“ LNAME”，“ L NAME”，“ LAST”，“ L”，“ LN”，“ LN”，“ SURNAME”，“ 4”
Suffix: "SUFFIX", "S", "5"
后缀：“ SUFFIX”，“ S”，“ 5”
GetAllNameParts takes three arguments:
GetAllNameParts具有三个参数：
NameStr is the full name to be parsed. Its assumed structure (i.e., name order) is determined by the FirstMiddleLast argument NameStr是要解析的全名。 它的假定结构（即名称顺序）由FirstMiddleLast参数确定
UseMiddle indicates whether or not a middle name might be present in the NameStr. If True or omitted, the function assumes that a middle name might be there. If False, it assumes there is never a middle name
FirstMiddleLast indicates the order of the name parts. If True or omitted, the function assumes: <Title (optional)> <First name> <Middle Name (optional)> <Surname> <Suffix (optional)>. If False, the function assumes: <Surname> <Suffix (optional)>, <First name> <Middle Name (optional)> FirstMiddleLast指示名称部分的顺序。 如果为True或省略，则该函数假定：<标题（可选）> <名字> <中间名（可选）> <姓氏> <后缀（可选）>。 如果为False，则函数假定：<姓氏> <后缀（可选）>，<名字> <中间名（可选）>
Please note the following (both functions):
请注意以下内容（两个功能）：
Name parts may be hyphenated or abbreviated. In the case of titles and suffixes, make sure that the abbreviated forms are included in the code
名称部分可以带连字符或缩写。 对于标题和后缀，请确保代码中包含缩写形式
The function has a pre-built list of Titles, which you can modify to fit your needs. The Titles string will be embedded in a RegExp Pattern string, so be sure to follow proper RegExp Pattern syntax
该功能具有一个预先建立的标题列表，您可以对其进行修改以适合您的需求。 Titles字符串将嵌入在RegExp Pattern字符串中，因此请确保遵循正确的RegExp Pattern语法
The function will recognize compound titles, as long as they are delimited by spaces
该函数将识别复合标题，只要它们之间用空格分隔即可
The function has a pre-built list of Suffixes, which you can modify to fit your needs. The Suffixes string will be embedded in a RegExp Pattern string, so be sure to follow proper RegExp Pattern syntax
该函数具有一个预先构建的后缀列表，您可以对其进行修改以适合您的需求。 后缀字符串将嵌入在RegExp Pattern字符串中，因此请确保遵循正确的RegExp Pattern语法
The function will recognize compound suffixes, as long as they are delimited by commas and/or spaces
该函数将识别复合后缀，只要它们以逗号和/或空格分隔即可
Both functions rely on a third, private function included in the source code above, GenerateLists, to create the master title and suffix lists. If you need to modify your master lists, make the changes there
这两个函数都依赖于上面的源代码GenerateList中包含的第三个私有函数来创建主标题和后缀列表。 如果您需要修改主列表，请在此处进行更改
If you are using True (or omitting) for FirstMiddleLast:
如果对FirstMiddleLast使用True（或省略）：
It is always assumed that the first name has a single "word"
始终假定名字只有一个“单词”
It is always assumed that the middle name, if present, has a single "word"
始终假定中间名（如果存在）具有单个“单词”
After the function has identfied the title, first name, middle name, and suffix, it assumes that whatever is left must be the surname/last name
在函数确定了标题，名字，中间名和后缀之后，它假定剩下的必须是姓/名
Thus, this function will process compound first/middle names incorrectly
因此，此功能将错误地处理复合名/中间名
If you are using False for FirstMiddleLast:
如果您对FirstMiddleLast使用False：
It is always assumed that the last comma in NameStr delimits the <Surname><Suffix> block from the <Title><First name><Middle name> block
始终假定NameStr中的最后一个逗号从<Title> <Firstname> <Middle name>块中分隔<Surname> <Suffix>块
Whatever is left in the <Surname><Suffix> block after the suffix has been removed is assumed to be the last name
删除后缀后，<Surname> <Suffix>块中剩下的所有内容均假定为姓氏
After the Title is removed from the <Title><First name><Middle name> block, if there is only one "word", it is the first name. If there are 2+ "words" and UseMiddle = True or omitted, then the last word is the middle name, and the rest is the first name
从<标题> <名字> <中间名>块中删除标题后，如果只有一个“单词”，则为名字。 如果有2个以上的“单词”，并且UseMiddle = True或省略，则最后一个单词为中间名，其余为名字
Thus, this function will process compound middle names incorrectly, and may erroneously think a compound first name is a first name and a middle name
因此，此函数将错误地处理复合中间名，并可能错误地认为复合名是名字和中间名
Special note: the master lists for titles and suffixes can get quite long, and thus some users may want to explore the possibility of storing these items in an external text file, a database table, or some other data management structure/service. Such an improvement is beyond the scope of this article, however.
特别说明：标题和后缀的主列表可能会很长，因此某些用户可能想探索将这些项目存储在外部文本文件，数据库表或某些其他数据管理结构/服务中的可能性。 但是，这种改进超出了本文的范围。
5.在您的VB6 / VBA项目中实现GetNamePart和GetAllNameParts (5. Implementing GetNamePart and GetAllNameParts in Your VB6/VBA Project)
To implement the GetNamePart function in your projects for VB6 or VBA (using Excel, Access, or any other program that uses VBA) application, please do the following:
要在您的项目中为VB6或VBA（使用Excel，Access或使用VBA的任何其他程序）应用程序实现GetNamePart函数，请执行以下操作：
Go to the Visual Basic Editor
转到Visual Basic编辑器
Add a new "regular" module to your project (not a class module!)
向您的项目中添加一个新的“常规”模块（不是类模块！）
Paste the code for GetNamePart, GetAllNameParts, and GenerateLists into that module
将GetNamePart，GetAllNameParts和GenerateLists的代码粘贴到该模块中
Update the title and/or suffix lists in the code if needed to support your specific data sets
如果需要支持特定数据集，请更新代码中的标题和/或后缀列表
Start using GetNamePart and/or GetAllNameParts in your code procedures, formulas, queries, and/or forms project
在代码过程，公式，查询和/或表单项目中开始使用GetNamePart和/或GetAllNameParts
Please note that, due to the difficulties in dealing with compound names, you should always spot check the results of the function.
For example, consider the names below, with compound name parts marked by (*):
例如，考虑以下名称，其复合名称部分用（*）标记：
First Middle Last
------------------------------------------------------
Anne Marie (*) Thomason Miller
John Ronald Reuel (*) Tolkien
John R.R. (*) Tolkien
John R. R. (*) Tolkien
Oscar de la Renta (*)
Jill St. John (*)
GetNamePart will struggle with those names, as seen in the results below:
如下面的结果所示，GetNamePart将与这些名称作斗争：
In both sample files, in addition to parsing out the various name parts, I also have a "Check" column that uses a simple test to see whether a particular record might be troublesome. For this test, I simply looked for records in which the middle name is found, or for which the extracted last name includes a space, as either condition can signal a compound name that should be verified by a human. I strongly recommend that you do something similar when using this function.
在两个示例文件中，除了解析各种名称部分之外，我还具有“检查”列，该列使用简单的测试来查看特定记录是否可能令人麻烦。 在此测试中，我只是寻找找到中间名或提取的姓氏包含空格的记录，因为任何一种情况都可以表示应由人验证的复合名。 我强烈建议您在使用此功能时执行类似的操作。
Despite the power and flexibility offered by GetNamePart and GetAllNameParts, you should always assume that the results will have to be audited by a person familiar with the data set and what it will be used for. Therefore, you should always retain a copy of the original data set to aid in the auditing process.
尽管GetNamePart和GetAllNameParts提供了强大的功能和灵活性，但您应始终假定结果必须由熟悉数据集及其用途的人员来审核。 因此，
6.进一步阅读 (6. Further Reading)
The GetNamePart and GetAllNameParts functions included in this article and in the sample files rely heavily on Regular Expressions to extract the name parts. Please see this article for more information on using Regular Expressions in VB6 and VBA.
本文和示例文件中包含的GetNamePart和GetAllNameParts函数在很大程度上依赖于正则表达式来提取名称部分。 请参阅本文，以获取有关在VB6和VBA中使用正则表达式的更多信息。
This article by Excel MVP Chip Pearson includes a user defined function to parse names, but it is not quite as flexible as the GetNamePart and GetAllNameParts functions provided in this article. That same page also includes some more Excel formulas for parsing names. Excel MVP Chip Pearson的这篇文章包括一个用户定义的函数来解析名称，但是它不如本文提供的GetNamePart和GetAllNameParts函数那么灵活。 该页面还包含更多用于解析名称的Excel公式。7.样本文件 (7. Sample Files)
For a demonstration of parsing names using the GetNamePart function, please download the sample files included here:
有关使用GetNamePart函数解析名称的演示，请下载此处包含的示例文件：
GetNamePart.xls
GetNamePart.xls
Parse Names Demo.mdb
解析名称Demo.mdb
Each file includes examples of parsing names in both the First Middle Last and Last, First Middle structures.
每个文件都包括在“ First Middle Last”和“ Last，First Middle”结构中解析名称的示例。
GetNamePart.xls GetNamePart.xls Parse-Names-Demo.mdb 解析名称-Demo.mdb =-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
If you liked this article and want to see more from this author, please click here.
如果您喜欢本文，并希望从该作者那里获得更多信息， 请单击此处 。
If you found this article helpful, please click the Yes button near the:
如果您认为这篇文章对您有所帮助 ，请单击以下位置旁边的“
Was this article helpful?
本文是否有帮助？
label that is just below and to the right of this text. Thanks!
此文字下方和右侧的标签。
=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
1213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
