EXCEL中提取每一个文本汉字的头一个大写字母的公式
3星 · 超过75%的资源 需积分: 5 151 浏览量
2012-09-24
14:43:05
上传
评论
收藏 14KB DOC 举报 
=IF(LEN(D26)>0,LOOKUP(MID(D26,1,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"妈","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"}),"")&IF(LEN(D26)>1,LOOKUP(MID(D26,2,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"呒","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"}),"")&IF(LEN(D26)>2,LOOKUP(MID(D26,3,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"呒","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"}),"")&IF(LEN(D26)>3,LOOKUP(MID(D26,4,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"呒","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";"夕","X";"丫","Y";"帀","Z"}),"")
=VLOOKUP(B2,
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"嘸","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"},2)&VLOOKUP(MID(B2,2,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"嘸","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"},2)&VLOOKUP(MID(B2,3,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"嘸","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";" 夕 ","X";" 丫 ","Y";" 帀 ","Z"},2)&VLOOKUP(MID(B2,4,1),
{"","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","
K";"垃","L";"嘸","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W
";"夕","X";"丫","Y";"帀","Z"},2)
如 A1 － A50 输 入 的 是 汉 字 。 则 在 B1 中 的 公 式 为 ：
=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"})B2－B50 填充。
批量生成 insert 語句
资源评论
sinasss2013-08-29可用 但定义太麻烦了
