excel表格里的姓名和身证号提取姓名拼音首字母+出生年月日
2024-05-31 05:19:35
公式为：
=LOOKUP(CODE(B2),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(B2,2,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&IFERROR(LOOKUP(CODE(MID(B2,3,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"}),"")&MID(C2,7,8)
如图所示：
2024-05-31 07:01:42
如果要做，用vba可以解决，文件丢过来。
2024-05-31 08:51:45
2024-05-31 07:41:29
下拉
2024-05-31 09:05:14
这个已经不是一般的公式能够解决得了的，需要用到宏处理转换为拼音的，也就是说需要有一个汉字转拼音的对应数据库才可以，如下图：
在B2单元格输入公式：
=IFERROR(LEFT(getpy(C2))&LEFT(REPLACE(getpy(C2),1,FIND(" ",getpy(C2)),))&LEFT(REPLACE(SUBSTITUTE(getpy(C2)," ",,1),1,FIND(" ",SUBSTITUTE(getpy(C2)," ",,1)),)),"")&MID(D2,9,6)
复制并下拉，即可。
由于表格使用了拼音转换的宏处理，所以必须要用我已经制作的表格，而且前提是必须要启用宏，否则不可能实现你的要求，除非你自己制作VBA
下面就是文档例样，你自己下载操作一下吧
热门标签
