Manik有一个工作表,该工作表的A列中的文本值格式为“ mikeDAVIS”,该人的名字用小写字母,姓氏用大写字母。根据文本的情况,他希望将名称分为两个单独的列。

这可以通过使用宏的公式来实现。不管使用哪种方法,关键都是要弄清楚文本从小写变为大写的位置。这只能通过检查字符串中的每个字符来完成。因此,如果要使用公式化方法,则需要使用数组公式。以下数组公式返回单元格A1中任何内容的姓氏:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)

* (CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

请记住,由于这是一个数组公式,因此应按Ctrl + Shift + Enter进行输入。它返回找到的第一个大写字母开头的单元格中的所有内容。因此,在“ mikeDAVIS”中将返回“ DAVIS”,在“ mikeDavis”中将返回“ Davis”。假设您在单元格B1中使用了数组公式,则可以使用以下方法确定名字:

=SUBSTITUTE(A1,B1,"")

这是一个常规公式,而不是数组公式。

有许多相似的数组公式可以完成很多相同的任务。例如,此数组公式将返回单元格A1中任何内容的名字(所有字符直到第一个大写字符):

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"& LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

然后,您可以使用相同的常规公式(使用SUBSTITUTE函数的公式)来得出姓氏。

如果要使用宏方法来查找名称,您所要做的就是提供一个公式,该公式将返回文本中第一个大写字母的位置。以下代码在文本中返回此“更改点”:

Function GetFirstUpper(MyCell As Range) As Integer     Dim sCellValue As String     Dim i As Integer

Application.Volatile     sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstUpper = 99     Else         GetFirstUpper = i     End If End Function

要使用该功能,我们假设名称在单元格A1中。您可以在工作表中使用以下公式找到名字和姓氏:

=LEFT(A1,GetFirstUpper(A1)-1)

=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

如果您希望宏返回实际名称,则可以使用下面的宏来返回第一个大写字母之前的所有内容:

Function GetFirstName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

Application.Volatile     sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstName = sCellValue     Else         GetFirstName = Left(sCellValue, i - 1)

End If End Function

要使用该宏,您需要做的就是在工作表单元格中使用以下内容。 (这假定要评估的文本字符串在单元格A1中。)

=GetFirstName(A1)

宏上的微小变化将使您能够类似地获取姓氏,假定姓氏是从遇到的第一个大写字母开始的所有内容。

Function GetLastName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

Application.Volatile     sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetLastName = sCellValue     Else         GetLastName = Mid(sCellValue, i)

End If End Function

如果愿意,可以将宏组合为一个函数,该函数将根据您指定的内容返回名字或姓氏:

Function GetName(MyCell As Range, sWanted As String) As String     Dim sCellValue As String     Dim i As Integer

Application.Volatile     sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetName = sCellValue     Else         If LCase(sWanted) = "first" Then             GetName = Left(sCellValue, i - 1)

Else             GetName = Mid(sCellValue, i)

End If     End If End Function

要使用此组合功能,您只需指定所需的名称:

=GetName(A1, "First")

以这种方式作为参数传递的单词“ First”将返回名字(第一个大写字母之前的所有内容)。作为第二个参数传递的任何其他字符串(例如“ Last”或“ xxx”或“ Rest”或什至“”)都将返回姓氏。

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(9089)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面(Excel 2007及更高版本)找到本技巧的版本: