在Excel中提取名称 [英] extract names in excel

问题描述

在我的excel文件列G中，我的名字是姓氏+名字+中间名.例如，Tasha，William P是样本名称，Tasha是姓氏，William是名字，P是中间名.我能够通过 = LEFT(G2，FIND(，"，G2)-1)提取姓氏，但无法分辨提取中间名和名字的好方法.如何使用excel公式提取名字和中间名?

In my excel file column G I have names with last name + first name + middle name. For example Tasha, William P is the sample name and Tasha is the Last name, William is the first name and P would be the middle name. I was able to pull last name by =LEFT(G2, FIND(",", G2)-1), but can't tell what would be the good ways to extract middle and first name. How can extract first and middle name with excel formula?

推荐答案

您可以使用此公式:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,",","")," ",REPT(" ",999)),(COLUMN(A:A)-1)*999+1,999))

将其放入B1，并复制3列并按数据长度向下复制.

Put it in B1 and copy over 3 columns and down the length of the data.

如果要按顺序排列它们，请使用以下方法:

If you want to put them in order then use this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,",","")," ",REPT(" ",999)),(CHOOSE(COLUMN(A:A),1,2,0))*999+1,999))

这篇关于在Excel中提取名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！