从邮件地址中提取姓名,我的超级公式,有这么复杂么?
标签:Excel公式练习
这是一个有趣的题目,假设电子邮件地址的格式如下:
名字任何非字母.字符姓氏@email.com
说明:
1.假设姓名中只有英文字母。
2.电子邮件地址只包含名字_分隔符_姓氏,没有中间名或其他前缀或后缀等。
3.电子邮件地址在单元格A1中。
4.假设单元格B1只包含电子邮件的姓名部分(即如果A1包含john_doe@email.com,则B1中为john_doe)。
5.在单元格C1和D1中,是需要提取的名字和姓氏。
示例数据如下图1所示。
图1
注:示例数据来源于chandoo.org。
看到这个示例后,我的第一反应就是利用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)
提取姓氏:
=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))))
都是数组公式,输入完后要按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(
继续……
相关文章
- Docker---配置阿里云镜像加速
- 2-docker软件部署
- 3-docker基础操作命令
- Docker---Docker基本命令
- 9.2领域事件
- Salesforce Spring '21 新功能 SOQL FIELDS()方法介绍
- Redis---Redis在Linux系统上的安装
- 5-docker镜像构建
- 多线程编程C语言版
- 服务器硬件RAID性能横评(2)
- 6-docker镜像仓库和标签tag
- Redis---Redis的五大基础类型
- Linux网络编程TCP
- 9.3DDD之集成事件
- 7-docker容器的网络通信
- Redis---Redis三种常用数据结构
- 使用Typora+PicGo配置Gitee图床
- 《领军行业大数据及AI实战》电子版地址
- Todo List: Client端与Server端交互,待办任务入库等 - 第六章
- netty学习(二)