在Microsoft Excel中分隔First Last Names姓名的9种方法

在 Excel 中需要分隔姓名吗？

包含人员姓名的数据集通常在一个单元格中包含名字和姓氏，以空格字符指定。

虽然这不一定是坏事，但将姓和名放在不同的列中可能会更有帮助。这样，您可以更轻松地对数据进行进一步分析，例如仅根据姓氏进行排序和筛选。

这篇文章将向您展示将名字和姓氏提取到 Excel 中不同单元格的所有方法！

使用文本将姓和名分隔到列

当您需要一次性将名字和姓氏拆分到数据中时，“文本到列”应该是您的首选方法。

文本分列功能可让您将名称拆分为新范围或替换当前数据。随您喜欢。

这最适合一次性使用的情况，因为您需要对添加到列表中的任何新名称再次执行文本到列的拆分。

在Microsoft Excel中分隔First Last Names姓名的9种方法

以下是如何使用文本到列来分隔名字和姓氏。

选择要拆分的名称范围。

这只能是单列范围。如果您尝试继续选择多列，您将收到一条弹出警告，告知您 Microsoft Excel 一次只能转换一列。

转到“数据”选项卡。
单击数据工具部分中的文本到列命令。

这将打开“将文本转换为列”向导，它将引导您完成拆分名称的步骤。

选择“分隔”选项来拆分您的姓名。
按下一步按钮。

取消选择默认的Tab选项。
选择空间选项。
单击下一步按钮。

当您选择“空格”选项时，您应该会看到在菜单的数据预览部分中出现一条黑线，将您的姓名分开。

为目标选择一个单元格。
单击“完成”按钮。

目标是 Excel 在工作表中放置名字和姓氏的位置，因此此处必须为空。选择一个单元格，该单元格将是 Excel 将放置所有姓名的范围最左上角的单元格。

如果您尝试使用已经有数据的目标，Excel 会发出警告并询问您是否要替换它。

Excel 将根据分隔名称的空格字符拆分名称，并将结果放在所需的位置。

使用文本公式分隔名字和姓氏

从全名中获取名字和姓氏的另一种方法是使用文本函数组合来解析每个名字。

您可以使用LEFT和RIGHT函数从任何文本值的开始或结束获取所需的字符数。

您可以将它们与FIND函数结合使用以获取空格字符的位置，并与LEN函数结合使用以获取名称中的字符总数。

获取名字

= LEFT ( B3, FIND ( " ", B3 ) - 1 )

上述公式将从单元格B3中的全名中提取名字。

FIND函数将查找第一个空格字符的位置。当您从此结果中减去1 时，您将得到第一个名字的最后一个字符的位置。

然后， LEFT函数使用该值返回名称中的前几个字符。

获取姓氏

= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) )

上述公式将从单元格B3中的全名中获取姓氏。

LEN函数获取全名的总字符数。FIND函数获取姓和名之间空格字符的位置。

当您用空格字符的位置减去姓名的总长度时，就会得到姓氏的字符数。

然后在 RIGHT 函数中使用它来提取姓氏。

使用 FILTERXML 函数分隔名字和姓氏

根据空格字符拆分文本的一种巧妙的方法是使用FILTERXML函数。

这是为了允许您根据节点名称提取 XML 数据的某些部分。

但是您可以使用它来提取姓名，方法是先根据您的姓名创建 XML 数据。

= TRANSPOSE ( FILTERXML ( "" & SUBSTITUTE ( B3," ","")&"","//s"))

上述公式将把单元格B3中的全名拆分为名字和姓氏。

FirstNameLastName

SUBSTITUTE函数用于用一些类似上述的文本替换空格。

FirstNameLastName

然后使用&将文本连接到名称的开头和结尾，以便得到类似上面的结果。

现在这是一个 XML 数据结构，您可以使用FILTERXML函数来提取每个名称。

= FILTERXML ( "FirstNameLastName","//s")

上述//s过滤器将提取 s 标签中包含的每个项目，这些项目现在是名字和姓氏。

这样就会生成一列姓名，您可以使用 TRANSPOSE 函数将其变成一行姓名。

仅获取名字

上面使用 FILTERXML 函数将拆分每个名称并生成两个单元格的数组。

但您可以稍微修改一下此公式，以便仅获取名字。

= FILTERXML ( "" & SUBSTITUTE ( B3, " ", "" ) & "", "//s[1]" )

上述版本的 FILTERXML 公式将仅返回名字。//s[1]过滤器将仅返回 s 标签中的第一个项目。

仅获取姓氏

= FILTERXML ( "" & SUBSTITUTE ( B3, " ", "" ) & "", "//s[2]" )

类似地，上述公式使用过滤器//s[2]返回 s 标签中的第二项，即姓氏。

使用 TEXTSPLIT 函数分隔名字和姓氏

如果您拥有较新版本的 Excel，那么您可以避免使用复杂的公式解决方案，因为有一个专门用于拆分文本的功能。

您可以使用TEXTSPLIT函数根据分隔符拆分文本值。

这意味着您可以使用空格字符作为分隔符来分隔名字和姓氏，

= TEXTSPLIT ( B3, " " )

上述公式将根据空格字符拆分单元格B3中的名称。

这真是太简单了！

使用快速填充功能分隔姓和名

Flash Fill是一种根据示例转换数据的便捷方法。

您在相邻列中提供一些所需结果的示例，然后Flash Fill将确定模式并填充其余部分。

当您提供示例结果时，这可以轻松地区分您的名字。

您可以查看有关Excel 快速填充的指南，了解有关此多功能数据工具的更多详细信息。

在相应全名右侧的单元格中单独输入几个名字。

这应该会触发“快速填充”，您将看到浅灰色的建议结果。按Enter接受这些结果并将其放入单元格中。

如果在前几次输入后Flash Fill没有自动触发，那么您可能需要手动触发它。

选择示例名字下方的第一个空单元格。

然后转到“数据”选项卡并单击“数据工具”部分中的“快速填充”命令。

提示：您也可以使用键盘快捷键Ctrl+E来使用快速填充。

现在您可以在下一列中重复该过程以获取姓氏。

注意：请注意，这些值是静态的，如果您更新全名列，它们将不会更新。您需要重新运行 Flash Fill 命令来更新它们。

使用 Power Query 分隔名字和姓氏

Power Query 是一款出色的工具，可以以几乎任何可以想象到的方式转换数据。

对于 Power Query 来说，拆分您的姓名是一项简单的任务！

如果您想要一个可重复的过程来在 Excel 中导入和转换数据，那么 Power Query 绝对是将名字和姓氏放入单独单元格的最佳方式。

在使用 Power Query 之前，您需要将您的姓名数据添加到 Excel 表中。

现在您可以使用 Power Query 拆分表中的名称。

转到“数据”选项卡。
单击“从表/范围”命令。

这将打开 Power Query 编辑器。

右键单击包含要拆分的名称的列的标题。
从选项中选择“拆分列” 。
在子菜单中选择按分隔符选项。

这将打开“按分隔符拆分列”菜单，您可以在其中选择用于拆分数据的分隔符。

从选择或输入分隔符下拉菜单中选择空格。
按下“OK”按钮。

就是这样。所有名字都已拆分，您应该有两个新列，分别包含名字和姓氏。

提示：双击新的列标题即可重命名它们。

现在您将能够将转换后的数据加载回 Excel。

转到“主页”选项卡。
单击“关闭并加载”。

这将打开“导入数据”菜单，您可以在其中选择要加载数据的位置。

选择表格选项。
选择现有工作表或新工作表来加载数据。
按下“OK”按钮。

然后，您的分离名称将加载到您选择的位置。

提示：如果您编辑、添加或删除源列表中的名称，您可以轻松更新分离的名称以反映更改。转到“数据”选项卡，然后单击“全部刷新”命令。

使用 Power Pivot 分隔名字和姓氏

如果您正在分析包含全名的数据，那么您可能需要使用 Power Pivot 来拆分姓名。

Power Pivot 数据模型可以在内存中容纳大量行，因此非常适合处理任何大型数据集。

将数据集加载到数据模型后，您可以使用 DAX 公式语言添加更多带有计算的列。

DAX 公式语言与 Excel 有很多通用函数，在这种情况下，所使用的函数将与前面看到的文本函数完全相同。

您可以将数据添加到数据模型。选择表格中包含您姓名的单元格，转到Power Pivot选项卡，然后单击“添加到数据模型”选项。

这将打开 Power Pivot 编辑器并将表加载到模型中。

您可以通过单击数据右侧的“添加列”标题来创建新列。

= LEFT ( Names[Full Name], FIND ( " ", Names[Full Name] ) - 1 )

上述公式将获取整个列的名字。

= RIGHT ( Names[Full Name], LEN ( Names[Full Name] ) - FIND ( " ", Names[Full Name] ) )

上述公式将返回整列的姓氏。

现在，您将能够在数据透视表中使用这些计算列。转到Power Pivot插件的“主页”选项卡，单击“数据透视表”命令，然后从菜单中选择“数据透视表”选项。

这些新的名字和姓氏计算列将作为数据透视表字段菜单中的字段提供。

使用 VBA 分隔名字和姓氏

也许您需要经常分隔名称，并且希望一键解决此问题。

VBA 可能是您的最佳解决方案。

您可以向工作簿添加一个宏，将名称拆分到相邻的列中。

可以通过将宏添加到快速访问工具栏来运行它，以便始终只需单击即可使用。

转到“开发人员”选项卡并单击“Visual Basic”命令。

您可以查看此文章，了解有关在功能区中启用“开发人员”选项卡的详细信息。或者，您可以按Alt+F11打开不带“开发人员”选项卡的 Visual Basic 编辑器。

这将打开 Visual Basic 编辑器。现在您可以在编辑器中创建一个新模块来添加代码。

转到Visual Basic 编辑器的插入菜单并选择模块选项。

Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer

colCount = Selection.Columns.Count
If colCount  1 Then
    MsgBox ("Select a single column!")
    End
End If

For Each rng In Selection
    arrNames = Split(rng.Value, " ")
    rng.Offset(0, 1).Value = arrNames(0)
    rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub

在 Visual Basic 编辑器中将上述代码添加到您的模块中。

此代码首先检查您是否只选择了包含一列的区域。如果您选择了多列，它将警告您选择单列，然后停止执行。

当选择单列时，代码将循环遍历选定的范围并根据空格字符拆分每个单元格。

拆分名称的第一部分和第二部分将输入到原始选择项右侧的单元格中。

选择姓名并运行代码后，您将在 Excel 中获得拆分的名字和姓氏。

使用 Office 脚本分隔姓和名

如果您主要在线使用 Excel，那么您将需要使用 Office 脚本创建自定义代码来分隔您的姓名。

可以创建类似的Office脚本。

转到“自动化”选项卡，然后单击“新建脚本”命令以打开Office 脚本的代码编辑器。

function main(workbook: ExcelScript.Workbook) {

    //getselected range
    let rng = workbook.getSelectedRange();
    let rows = rng.getRowCount();
    let cols = rng.getColumnCount();

    if (cols != 1) {
        return;
    };

    //loop through selected cells
    for (let i = 0; i

将上述代码添加到代码编辑器并按保存按钮。

现在您可以在 Excel 表中选择单个列范围的名称并按“运行”按钮。

代码将检查您的选择是否是单列，如果是，则停止运行代码。

否则，代码将继续循环遍历您选择的单元格以拆分姓名。名字和姓氏将添加到您选择的单元格右侧的列中。

结论

许多数据集都包含姓名字段。有时，姓名字段会以全名的形式出现在单个单元格中，您需要将姓名分为名字和姓氏两个部分。

文本到列或快速填充只需几个简单的步骤即可完成工作，非常适合一次性使用。

公式解决方案是一种更动态的解决方案，更适合于添加或编辑源数据集名称的情况。您使用哪种公式取决于您的 Excel 版本中有哪些可用的函数。

对于需要首先导入 Excel 的较大数据集或外部数据集，Power Query 或 Power Pivot 解决方案将发挥最佳作用。

在Microsoft Excel中分隔First Last Names姓名的9种方法