excel提取汉字的拼音首字母集锦

1、 方法二：VBA自定义函数n=hztopy () n 下面是一个VBA自泄义函数，工具宏.Visual Basic编辑器插入模块，在VBA编辑器 里而插入-模块贴入下述代码，那么，比如在A3中有进退两难，则在B3中输入 M=hztopy (A3) % 则会得到TTLN”。 不过此法有时候也会岀错，比如”皓、鑫、婷、雯、奕”等，就无法提取汉字拼音首字母。 Function hztopy(hzpy As String) As String Dim hzstring As String, pystring As String Dim hzpysum As Integer, hzi As Intege

2、r, hzpyhex As Integer hzstring = Trim(hzpy) hzpysum = Lcn(Trim(hzstring) pystring = For hzi = 1 To hzpysum hzpyhex = H&HM + Hex(Asc(Mid(hzstring, hzi, 1) Select Case hzpyhex Case &HB0A1 To &HB0C4: pystring = pystring + Case &HB0C5 To &HB2C0: pystring = pystring + ”B” Case &HB2C1 To &HB4ED: pystring

3、= pystring + HCH Case &HB4EE To &HB6E9: pystring = pystring + Case &HB6EA To &HB7A1: pystring = pystring + HEU Case &HB7A2 To &HB8C0: pystring = pystring + ”F Case &HB8C1 To &HB9FD: pystring = pystring + ”G” Case &HB9FE To &HBBF6: pystring = pystring + nH Case &HBBF7 To &HBFA5: pystring = pystring +

4、 HJH Case &HBFA6 To &HCOAB: pystring = pystring + Kn Case &HCOAC To &HC2E7: pystring = pystring + HLN Case &HC2E8 To &HC4C2: pystring = pystring + ”NT Case &HC4C3 To &HC5B5: pystring = pystring + HNH Case &HC5B6 To &HC5BD: pystring = pystring + HOM Case &HC5BE To &HC6D9: pystring = pystring + P” Cas

5、e &HC6DA To &HC8BA: pystring = pystring + HQM Case &HC8BB To &HC8F5: pystring = pystring + * R Case &HC8F6 To &HCBF9: pystring = pystring + MSH Case &HCBFATo &HCDD9: pystring = pystring + T Case &HEDC5: pystring = pystring + HTH Case &HCDDA To &HCEF3: pystring = pystring + HWM Case &HCEF4 To &HD 1B8

6、: pystring = pystring + Case &HDIB9 To &HD4D0: pystring = pystring + MYH Case &HD4DI To &HD7F9: pystring = pystring + HZH Case Else pystring = pystring + Mid(hzstring, hzi, I) End Select Next hztopy = pystring End Function 方法三：VBA自定义函数n=getpy ()n 下而是一个VBA自泄义函数，工具宏-Visual Basic编辑器亠 插入模块，在VBA编辑 器里而-插入

7、-模块-贴入上述代码，那么。 那么，比如在A2中有进退两难”,则在B2中输入=getpy(A2)M,则会得到JTLN”。 不过此法有时候也会出错，比如”鑫、雯、奕等，就无法提取汉字拼音首字母，还有些字如 ”皓、婷”等被错提取为“Z”。 Function getpychar(char) tmp = 65536 + Asc(char) If (tmp = 45217 And tmp = 45253 And tmp = 45761 And tmp = 46318 And tmp = 46826 And tmp = 47010 And tmp = 47297 And tmp = 47614 And t

8、mp = 48119 And tmp = 49062 And tmp = 49324 And tmp = 49896 And tmp = 50371 And tmp = 50614 And tmp = 50622 And tmp = 50906 And tmp = 51387 And tmp = 51446 And tmp = 52218 And tmp = 52698 And tmp = 52980 And tmp = 53689 And tmp = 54481 And tmp = 62289) Then getpychar = HZH Else如果不是中文，则不处理 getpychar =

9、 char End If End Function Function getpy(str) For i = 1 To Len(str) getpy = getpy & getpychar(Mid(str, i, 1) Next i End Function 方法四：Excel函数LOOKUP 这个函数在Excel里而最多只能设置自动提取字段的前四个字的拼音首字母，多了会提示公 式太长。 比如在A2中有”进退两难”，则在B2中输入如下函数LOOKUP(八 则会得到nJTLN% 提取前四个字首字母时的函数 =LOOKUP(CODE(A2),45217+ 0,36,544,1101,1609,179

10、3,2080,2560,2902,3845,4107,4679,5154 ,5397,5405,5689,6170,6229,7001,7481,7763,84729264,HAVB TCDETFTGTHTJT KTLTMTNTOTPTQTRTSTTWTXYTZJ)&LOOKUP(COD E(MID(A2,2,1),4 3 5217+0,36,544101,1609J 7932080.25602902,3845,4107,4679,5154,5397,5405,5689,6170,62 29,7001,7481,7763,8472,9264, rAJBTCTDTETFTGTHTJTKTLTMT

11、NTOTP”, ,qr；STTYTZ” )&LOOKUP(CODE(MID( A2,3,1 )45217+ 0,36,544,11016 09 J 793,2080,2560.2902,38454107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472. 9264, T YTZ )&LOOKUP(CODE( MID(A2A1 ),45217+ 036.5444101,1609,1793,2080.2560.2902 ,3845,4107,46795154,5397.5405,5689.6170,6229,7001,7481,7763.8472,9264, JETFTGTHTJ； KL；MTNTOTPQTR；STTT WXT YTZ”) 提取前二个字首字母时的函数 =LOOKUP(CODE( A2),45217+0,36,544,110146094793,2080,2560,2902,3845,4107,4679,5154 ,5397,5405,5689.6170,6229,7001,7481,7763,8472.9264,TCTDTETFTGTHTJ 丁 KTLTMJNTOTPTQTRTSTTTWTXTYTZ”)&L00KUP(C0DE(MID(A221),4 5217+0,36,544,1101,1609J 793