在Microsoft Excel中反转切换文本的8种方法
您需要用逗号来交换名字和姓氏吗?
处理联系人数据时遇到的问题之一是其全名的格式。对于全名显示为First Last的联系人,您可能更愿意将其格式化为Last, First。
这样做通常可以更轻松地对列表进行排序和筛选。此外,姓氏通常比名字更独特,这使得任何查找都更可靠。
这篇文章探讨了在 Excel 中反转名字和姓氏的几种不同方法。
推荐:如何修复Windows禁用UAC时无法激活此应用程序错误
将带有文本的名字和姓氏切换至列
切换名字和姓氏的一种方法是通过“文本到列”功能。
此功能可以将名字和姓氏分离到各自的单元格中,然后可以使用公式将它们重新组合成新格式。
首先,选择包含您的联系人的单元格。
转到数据功能区选项卡并单击文本到列。
确保选择“分隔”作为“原始数据类型”。这是因为空格字符充当名字和姓氏之间的分隔符。
准备好后单击下一步按钮。
在上述分隔符中,选中空格选项作为分隔符,并清除所有其他分隔符复选框。
数据预览显示 Excel 如何分隔名称。
单击下一步按钮。
选择目标文本框,然后选择一个单元格作为分隔名称的起点。
在上面的例子中,第一个联系人的名字将进入单元格,$C$3
而姓氏将进入相邻的右侧单元格。其余联系人将以类似的方式向下分开。
单击完成按钮。
正如您上面所看到的,名字和姓氏已被分成各自的列。
= D3 & ", " & C3
下一步是重新组合列以切换名称。
在第一个联系人的行中,选择一个空单元格并将上述公式粘贴到公式栏中。
“与”号 ( &
) 将单元格 D3 中的姓氏与单元格 D3 中的名字连接起来,中间C3
用逗号 ( ) 分隔。,
按下Enter计算公式。
要将公式应用于其他联系人,请将鼠标悬停在公式单元格的右下角,直到光标变成加号 ( +
),然后单击左键并按住鼠标按钮向下拖动。
您的联系人姓名现已切换!
推荐:如何在Microsoft Excel中使用规划求解插件
使用文本公式切换名字和姓氏
如果您宁愿使用仅公式的解决方案来切换名称,那么有一种方法可以做到。
这样的公式将附加FIND、LEN、RIGHT和LEFT函数的结果,以将每个名称格式化为新单元格。
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。
确保活动单元格仍然位于示例的列内。
转到数据功能区选项卡并单击快速填充,或者直接按Ctrl+ E。
神奇的是,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 脚本作为灵活的编码选项,具体取决于您使用的是桌面应用程序还是在线应用程序。