从Excel字段中解析名字和姓氏 [英] Parsing out First and Last name from Excel field

问题描述

我在Excel中有一个字段(列)，格式为姓氏，名字中间字母"，姓氏和名字后面的逗号之间有一个空格，中间名首字母和名字之间有第二个空格(否名字后面的逗号).有没有办法识别哪些单元格在右侧具有中间的首字母，然后消除所有单元格的中间的首字母，以使输出看起来像"LastName，FirstName"?

I have a field (column) in excel in the format of "LastName, FirstName MiddleInitial" with a space between the comma after the last name and the first name and a second space between the middle initial and the first name (no comma after the first name). Is there a way to identify which cells have a middle initial on the right hand side and then eliminate the middle initial for all cells such that the output will look like "LastName, FirstName"?

谢谢！

推荐答案

您想要做的是能够将字段解析为多个字段，然后使用简单的excel公式重新加入.要获取一个字段并将其放入多列中，您可以对文本到列"使用以下方法

What you want to do is to be able to parse the field into multiple fields, and then rejoin then with a simple excel formula. To take a field and put it into multiple columns you can use the following approach for "Text to Columns"

文本到列

一旦您有3列文本，就可以通过使用要具有该值的列中的= A1&，"& B1之类的公式，将前两列组合在一起. (在我的示例中，姓氏在A1中，名字在B1中)

Once you have 3 columns of text, you can group the first two together by using a formula like =A1&", "&B1 in the column you want to have the value. (In my example, the last name would be in A1, and first name in B1)

这篇关于从Excel字段中解析名字和姓氏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！