在Microsoft Excel中反转切换文本的8种方法

在Microsoft Excel中反转切换文本的8种方法

您需要用逗号来交换名字和姓氏吗?

处理联系人数据时遇到的问题之一是其全名的格式。对于全名显示为First Last的联系人,您可能更愿意将其格式化为Last, First

这样做通常可以更轻松地对列表进行排序和筛选。此外,姓氏通常比名字更独特,这使得任何查找都更可靠。

这篇文章探讨了在 Excel 中反转名字和姓氏的几种不同方法。

推荐:如何修复Windows禁用UAC时无法激活此应用程序错误

将带有文本的名字和姓氏切换至列

切换名字和姓氏的一种方法是通过“文本到列”功能。

此功能可以将名字和姓氏分离到各自的单元格中,然后可以使用公式将它们重新组合成新格式。

在Microsoft Excel中反转切换文本的8种方法

首先,选择包含您的联系人的单元格。

转到数据功能区选项卡并单击文本到列

确保选择“分隔”作为“原始数据类型”。这是因为空格字符充当名字和姓氏之间的分隔符。

准备好后单击下一步按钮。

在上述分隔符中,选中空格选项作为分隔符,并清除所有其他分隔符复选框。

数据预览显示 Excel 如何分隔名称。

单击下一步按钮。

选择目标文本框,然后选择一个单元格作为分隔名称的起点。

在上面的例子中,第一个联系人的名字将进入单元格,$C$3而姓氏将进入相邻的右侧单元格。其余联系人将以类似的方式向下分开。

单击完成按钮。

正如您上面所看到的,名字和姓氏已被分成各自的列。

= D3 & ", " & C3

下一步是重新组合列以切换名称。

在第一个联系人的行中,选择一个空单元格并将上述公式粘贴到公式栏中。

“与”号 ( &) 将单元格 D3 中的姓氏与单元格 D3 中的名字连接起来,中间C3用逗号 ( ) 分隔。,

按下Enter计算公式。

要将公式应用于其他联系人,请将鼠标悬停在公式单元格的右下角,直到光标变成加号 ( +),然后单击左键并按住鼠标按钮向下拖动。

您的联系人姓名现已切换!

推荐:如何在Microsoft Excel中使用规划求解插件

使用文本公式切换名字和姓氏

如果您宁愿使用仅公式的解决方案来切换名称,那么有一种方法可以做到。

这样的公式将附加FINDLENRIGHTLEFT函数的结果,以将每个名称格式化为新单元格。

FIND ( " " , B3 )

给定单元格B3中的全名,上述FIND函数返回空格字符的位置(从左侧开始)。

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

要计算从右边开始的空格字符的位置,需要从全名的长度中减去FIND结果。

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

使用上面的RIGHT函数提取姓氏。

LEFT ( B4 , FIND ( " " , B4 ) - 1 )

提取名字的方法类似,如上所示。但会从FIND1结果中减去,这样空格字符就会被排除。

= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) ) & ", " & LEFT ( B3 , FIND ( " " , B3 ) - 1 ) 

将所有这些放在一起,将上述公式粘贴到第一个联系人右侧的空单元格中。该公式将姓氏和名字连接在一起,,中间用逗号 ( ) 隔开。

按下Enter即可触发公式计算。

要将公式应用于其他联系人,请将鼠标悬停在公式单元格的右下角,直到光标变成加号 ( + ),然后单击左键并按住鼠标按钮向下拖动。

瞧!您的姓名格式已完成!

使用 TEXTSPLIT 函数切换名字和姓氏

TEXTSPLIT是 Excel 365 测试版 2203 中引入的新功能。

TEXTSPLIT函数将一个单元格值拆分为多个单元格值。它通过公式为您提供“文本分列”功能。

TEXTSPLIT ( B3 , " " )

上面的TEXTSPLIT在单元格B3中查找联系人内的空格字符。空格左侧的所有字符都将作为一个项目提取,右侧的所有字符也是如此。

结果是一个包含两个项目的数组,其中保存着名字和姓氏。

SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) )

COUNTA函数计算数组项的数量,对于大多数名称来说,数量为两个。

SEQUENCE函数使用该计数创建一个包含两个项目的序列数字数组。这将用于反转名称的顺序。

SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 )

两个数组都提供给SORTBY函数,该函数根据相应的数字重新排列名称。

强制-1按相反顺序排序,有效地切换名称!

TRANSPOSE是必需的,因为SORTBY期望名称数组像数字数组一样垂直填充。

= TEXTJOIN ( ", " , TRUE , SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 ) )

公式的最终版本如上。将此版本粘贴到公式栏中。

上述TEXTJOIN函数将交换名称数组转换为单个全名,包括嵌入的逗号 ( ,)。

剩下的唯一步骤就是将公式复制到其他联系人。将鼠标悬停在公式单元格的右下角,直到光标变成加号 ( +),然后单击鼠标左键并按住鼠标按钮向下拖动。

现在,您已将所有联系人的姓名翻转,中间带有逗号!

使用“快速填充”切换姓和名

快速填充功能是切换姓氏和名字的绝佳方式,无需编写复杂的公式。

通过提供一些已切换名称的示例,Flash Fill 可以从您的示例中学习并自动切换您剩余的名称!

首先选择第一个联系人右侧的空白单元格,启动“快速填充”。在该单元格中,输入联系人的姓名(姓和名互换)。然后按Enter

确保活动单元格仍然位于示例的列内。

转到数据功能区选项卡并单击快速填充,或者直接按CtrlE

神奇的是,Flash Fill 可以识别出您想要交换名字和姓氏,并会为您提供结果预览!

要接受结果,请点击单元格右下角的“快速填充选项”菜单,然后点击“接受建议”

你们都完成了,名字都颠倒了!

使用 Power Query 切换名字和姓氏

Power Query 是 Excel 的一项功能,可让您对数据执行复杂的转换。

要切换名字和姓氏,您可以向 Power Query 提供一些示例,一旦识别出模式,Power Query 将为您完成任务并创建转换所需的公式!

要启动 Power Query,首先选择您的联系人列,包括您的标题。

转到数据功能区选项卡并单击从表/范围

Power Query 编辑器窗口中,转到“添加列”选项卡,然后单击“来自示例的列”

您需要提供足够的示例以便 Power Query 识别开关模式。

在右列第一行输入您的第一个示例。例如,如果您的第一个联系人是“Trent Buckston”,请输入“Buckston, Trent”,如上所示。

Enter键入后按。

 注意:您不需要从第一行开始按顺序提供示例。您可以在任意行中提供示例。

在第二行输入第二个示例以进一步帮助 Power Query。

再次Enter输入后按。

请注意,Power Query 如何使用您的示例来猜测剩余的行。结果并不完全符合您的要求,因此您需要提供其他示例。

在您提供第三个示例后,您最终将看到所需的结果!

选择自定义列标题并输入描述性名称(如Switched ),然后按下Enter并单击确定按钮。

请注意,原始的“全名”列仍会出现。由于您的工作表上已经有“全名”列,因此您可以删除该列。

选择该列并转到“主页”选项卡,然后单击“删除列”命令。

通过点击“关闭并加载”下拉菜单,然后点击“关闭并加载到”,“已切换”列放到工作表中。

“导入数据”窗口中,选择“表格”选项,然后选择“现有工作表”。突出显示其文本框内容,然后选择工作表上的一个空单元格。此单元格将成为结果的第一行。

完成后,单击“确定”按钮。

正如您在上面看到的,您的交换姓名列现在已出现在您的工作表上!

推荐:WordPress加速插件WP Plugin Manager Pro

使用 Power Pivot 切换名字和姓氏

Power Pivot 是 Excel 的一个插件,可以为大量数据添加商业智能。

使用 Power Pivot,您可以添加交换名称的计算列,然后使用数据透视表显示该列。

默认情况下,Power Pivot 在 Excel 中处于禁用状态。但您可以启用它以将Power Pivot 选项卡添加到 Excel 功能区。

选择您的联系人数据,转到Power Pivot功能区选项卡并单击“添加到数据模型”

您的联系人数据现在显示为 Power Pivot 中的表格列。双击底部选项卡并将其重命名为名称,可以给此表赋予更具描述性的名称。

Enter完成后按下。

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

要创建交换名称的列,请选择标有“添加列”的列。

将上述公式粘贴到公式栏中。此公式执行的操作与本文前面看到的文本公式解决方案相同。

主要区别在于 Power Pivot 使用名为DAX的公式语言来一次引用整个列!

例如,公式使用语法引用名称表的名列。Names[Full Name]

按下后Enter,您所有切换的名称都会出现在新列中!

双击列标题并输入Switched ,为新列指定一个更具描述性的名称。

Enter完成后按下。

现在,您就可以在工作表上显示新列了。转到“主页”选项卡,然后单击“数据透视表”

选择数据透视表的目标位置。在上面的示例中,将创建一个新的工作表。

准备好后单击“确定”按钮。

“数据透视表字段”窗格中,深入到“名称”表并检查“已切换”字段,以便该字段出现在“行”区域中。

您切换后的名称现在位于工作表的数据透视表内!

使用 VBA 切换名字和姓氏

Excel 的 VBA 编程语言为您提供了一种高度自动化、复杂的解决方案来切换您的姓名。

通过 VBA,您可以创建代码来迭代每个联系人,然后以相反的顺序分离并重新连接他们的姓名,然后将其输出到新列。

要将此类代码添加到您的工作簿,请按Alt+打开 VBA 编辑器F11

将自定义代码与工作簿本身分开是一个好主意,因此可以通过选择“插入”菜单,然后选择“模块”来创建模块。

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(1) & ", " & arrNames(0)
Next rng
End Sub

双击新模块将其打开,然后将上述 VBA 代码粘贴到模块中。

在运行时,上述代码首先确保您只选择了工作表上的一列联系人。任何其他选择都会导致执行结束并发出通知。

代码循环遍历每个选定的单元格,并分隔名字和姓氏。分隔通过函数在空格字符的位置进行Split

这两个名称都放在名为 的数组中arrNames。使用与号 ( ) 以相反的顺序重新连接数组元素&,并将其放在由 表示的相邻单元格中rng.Offset(0, 1)

当此类 VBA 代码可从工作表中运行时,它通常被称为宏。

要运行此宏,请返回您的工作表并确保已选择联系人列。

选择“视图”功能区选项卡并单击“宏”

对话框中,选择您的SplitNames宏并单击运行按钮。

运行完成后,您可以看到相邻的列现在包含名称反转的联系人!

推荐:Microsoft Excel中将表格另存为图像的5种方法

使用 Office 脚本切换姓和名

随着 Microsoft 365 网页版的出现,Excel 商业计划用户可以使用一种名为Office Scripts 的新脚本语言。

您可以利用 Office 脚本来自动执行诸如切换姓和名之类的任务。

要启动您自己的脚本,请从您的网络浏览器打开您的工作簿。

选择 自动化功能 区选项卡,然后单击 新建脚本

如果您没有看到“自动化”选项卡,请检查您的 Microsoft 365 许可证,以确保您拥有访问 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 

在出现的代码编辑器窗格中,用上述脚本替换其全部内容。

 注意:您main的脚本中始终需要一个函数,因为这是每个脚本的起点。

该脚本首先确保您只选择了一列联系人,否则脚本将终止。

脚本循环遍历每个联系人。通过查找空格字符的位置来提取名字和姓氏。

两个姓名以相反的顺序重新组合,中间用逗号 ( ,) 隔开。结果将放入相邻单元格,然后再处理下一个联系人。

在运行脚本之前,请转到工作表并选择联系人列。

单击代码编辑器窗格中的运行按钮来执行代码。

完成后,您可以看到相邻列中的联系人姓名已切换!

结论

在这篇文章中,您已经了解了在 Excel 中切换名字和姓氏的多种方法。

文本到列功能提供了干净、交互式的体验,而文本公式方法的优点是步骤更少,并且如果源名称发生变化,结果将动态更新。

涉及TEXTSPLIT的公式向您展示了 Excel 动态数组函数的优势。但请记住,并非所有用户都拥有此功能。

快速填充功能非常适合于切换名称等模式化任务,但当您的数据格式不一致时可能会失效。

对于较大的数据源,您可以选择 Power Query 或 Power Pivot。Power Query 具有强大的按示例列功能,因此不需要太多公式知识。

否则,您可以使用 VBA 和 Office 脚本作为灵活的编码选项,具体取决于您使用的是桌面应用程序还是在线应用程序。

推荐:如何使用md-block在网页上呈现Markdown


发表评论