excelperfect
标签：Excel公式练习
名字任何非字母.字符姓氏@email.com
说明：
1.假设姓名中只有英文字母。
4.假设单元格B1只包含电子邮件的姓名部分（即如果A1包含john_doe@email.com，则B1中为john_doe）。
5.在单元格C1和D1中，是需要提取的名字和姓氏。
示例数据如下图1所示。
图1
看到这个示例后，我的第一反应就是利用ASCII字符代码，因为小写字母的字符代码在97与122之间，因此，我可以获取“@”前的字符，看其是否在97与122之间，从而提取出相应的字符串。
提取“@”前的字符很简单，利用FIND函数找到“@”的位置，再用LEFT函数即可提取：
LEFT(B4,FIND(“@”,B4)-1)
然后，将其拆分成单个字符，使用经典的MID/ROW函数组合：
MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1)
再看其是否在97与122之间：
CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1))>96
和
CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1))<123
将两者相乘：
(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”&FIND(“@”,B4)-1)),1))>96)*(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1))<123)
得到一个由1/0值组成的数组，1值表明为小写字母字符，0值表明为其它字符。
找到0首次出现的位置，减1，即为名字的最后一个字符的位置：
MATCH(0,(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”&FIND(“@”,B4)-1)),1))>96)*(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1))<123),)-1
这样，使用LEFT函数即可提取出名字：
=LEFT(B4,MATCH(0,(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”&FIND(“@”,B4)-1)),1))>96)*(CODE(MID(LEFT(B4,FIND(“@”,B4)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-1)),1))<123),)-1)
这是一个数组公式，输入完后要按Ctrl Shift Enter组合键。
利用提取出的名字，去掉前面的名字部分，然后按相同的思路，提取姓氏。假设上面提取名字的公式在单元格I12，则提取姓氏的公式为：
=TRIM(MID(MID(B4,LEN(I12) 1,FIND(“@”,B4)-LEN(I12)-1),MATCH(1,(CODE(MID(MID(B4,LEN(I12) 1,FIND(“@”,B4)-LEN(I12)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-LEN(I12)-1)),1))>96)*(CODE(MID(MID(B4,LEN(I12) 1,FIND(“@”,B4)-LEN(I12)-1),ROW(INDIRECT(“1:”& FIND(“@”,B4)-LEN(I12)-1)),1))<123),),LEN(B4)))
这也是一个数组公式，输入完后要按Ctrl Shift Enter组合键。
公式看起来超复杂，其实原理很简单。
还可以这样。提取名字：
=LEFT(LEFT(B4,FIND(“@”,B4)-1),MIN(IF(ISERROR(FIND(MID(LEFT(B4,FIND(“@”,B4)-1),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99)))-1)
提取姓氏：
=RIGHT(LEFT(B4,FIND(“@”,B4)-1),LEN(LEFT(B4,FIND(“@”,B4)-1))-MAX(IF(ISERROR(FIND(MID(LEFT(B4,FIND(“@”,B4)-1)&REPT(“a”,100),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99))))
都是数组公式，输入完后要按Ctrl Shift Enter组合键。
还有简洁优雅的公式吗？
看看下面的数组公式。提取名字：
=LEFT(B4,MATCH(1=1,MID(B4,ROW($1:$99),1)<“a”,)-1)
提取姓氏：
=RIGHT(LEFT(B4,FIND(“@”,B4)-1),MATCH(1=1,MID(LEFT(B4,FIND(“@”,B4)-1),LEN(LEFT(B4,FIND(“@”,B4)-1))-ROW($1:$99),1)<“a”,))
继续……
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
