excel中文转成拼音字母（包括首字母大写）
参考文献：
1.首字母大写：http://www.excelpx.com/thread-168029-1-1.html（里面下载一个excel，里面有宏）
中文转拼音：
2.http://blog.sina.com.cn/s/blog_4bca87330100n8c3.html（之前用过可行）
3.http://www.gaohaipeng.com/1706.html（这个博主说上面的不能完美转换，找了另外的）
操作过程
参考文献1中的函数
普通输出：=chinese2spell(A2,1) //参数为1
首字母大写：=chinese2spell(A2,0) //参数为0
去除空格：=SUBSTITUTE(chinese2spell(A2,0),” “,””)
1 Public Function Chinese2Spell(sChinese As String, Optional iflag As Integer = 0) As String 2 Dim C2S As String 3 Set d = CreateObject("Scripting.Dictionary") 4 d.Add "a", -20319 5 d.Add "ai", -20317 6 d.Add "an", -20304 7 d.Add "ang", -20295 8 d.Add "ao", -20292 9 d.Add "ba", -20283 10 d.Add "bai", -20265 11 d.Add "ban", -20257 12 d.Add "bang", -20242 13 d.Add "bao", -20230 14 d.Add "bei", -20051 15 d.Add "ben", -20036 16 d.Add "beng", -20032 17 d.Add "bi", -20026 18 d.Add "bian", -20002 19 d.Add "biao", -19990 20 d.Add "bie", -19986 21 d.Add "bin", -19982 22 d.Add "bing", -19976 23 d.Add "bo", -19805 24 d.Add "bu", -19784 25 d.Add "ca", -19775 26 d.Add "cai", -19774 27 d.Add "can", -19763 28 d.Add "cang", -19756 29 d.Add "cao", -19751 30 d.Add "ce", -19746 31 d.Add "ceng", -19741 32 d.Add "cha", -19739 33 d.Add "chai", -19728 34 d.Add "chan", -19725 35 d.Add "chang", -19715 36 d.Add "chao", -19540 37 d.Add "che", -19531 38 d.Add "chen", -19525 39 d.Add "cheng", -19515 40 d.Add "chi", -19500 41 d.Add "chong", -19484 42 d.Add "chou", -19479 43 d.Add "chu", -19467 44 d.Add "chuai", -19289 45 d.Add "chuan", -19288 46 d.Add "chuang", -19281 47 d.Add "chui", -19275 48 d.Add "chun", -19270 49 d.Add "chuo", -19263 50 d.Add "ci", -19261 51 d.Add "cong", -19249 52 d.Add "cou", -19243 53 d.Add "cu", -19242 54 d.Add "cuan", -19238 55 d.Add "cui", -19235 56 d.Add "cun", -19227 57 d.Add "cuo", -19224 58 d.Add "da", -19218 59 d.Add "dai", -19212 60 d.Add "dan", -19038 61 d.Add "dang", -19023 62 d.Add "dao", -19018 63 d.Add "de", -19006 64 d.Add "deng", -19003 65 d.Add "di", -18996 66 d.Add "dian", -18977 67 d.Add "diao", -18961 68 d.Add "die", -18952 69 d.Add "ding", -18783 70 d.Add "diu", -18774 71 d.Add "dong", -18773 72 d.Add "dou", -18763 73 d.Add "du", -18756 74 d.Add "duan", -18741 75 d.Add "dui", -18735 76 d.Add "dun", -18731 77 d.Add "duo", -18722 78 d.Add "e", -18710 79 d.Add "en", -18697 80 d.Add "er", -18696 81 d.Add "fa", -18526 82 d.Add "fan", -18518 83 d.Add "fang", -18501 84 d.Add "fei", -18490 85 d.Add "fen", -18478 86 d.Add "feng", -18463 87 d.Add "fo", -18448 88 d.Add "fou", -18447 89 d.Add "fu", -18446 90 d.Add "ga", -18239 91 d.Add "gai", -18237 92 d.Add "gan", -18231 93 d.Add "gang", -18220 94 d.Add "gao", -18211 95 d.Add "ge", -18201 96 d.Add "gei", -18184 97 d.Add "gen", -18183 98 d.Add "geng", -18181 99 d.Add "gong", -18012 100 d.Add "gou", -17997 101 d.Add "gu", -17988 102 d.Add "gua", -17970 103 d.Add "guai", -17964 104 d.Add "guan", -17961 105 d.Add "guang", -17950 106 d.Add "gui", -17947 107 d.Add "gun", -17931 108 d.Add "guo", -17928 109 d.Add "ha", -17922 110 d.Add "hai", -17759 111 d.Add "han", -17752 112 d.Add "hang", -17733 113 d.Add "hao", -17730 114 d.Add "he", -17721 115 d.Add "hei", -17703 116 d.Add "hen", -17701 117 d.Add "heng", -17697 118 d.Add "hong", -17692 119 d.Add "hou", -17683 120 d.Add "hu", -17676 121 d.Add "hua", -17496 122 d.Add "huai", -17487 123 d.Add "huan", -17482 124 d.Add "huang", -17468 125 d.Add "hui", -17454 126 d.Add "hun", -17433 127 d.Add "huo", -17427 128 d.Add "ji", -17417 129 d.Add "jia", -17202 130 d.Add "jian", -17185 131 d.Add "jiang", -16983 132 d.Add "jiao", -16970 133 d.Add "jie", -16942 134 d.Add "jin", -16915 135 d.Add "jing", -16733 136 d.Add "jiong", -16708 137 d.Add "jiu", -16706 138 d.Add "ju", -16689 139 d.Add "juan", -16664 140 d.Add "jue", -16657 141 d.Add "jun", -16647 142 d.Add "ka", -16474 143 d.Add "kai", -16470 144 d.Add "kan", -16465 145 d.Add "kang", -16459 146 d.Add "kao", -16452 147 d.Add "ke", -16448 148 d.Add "ken", -16433 149 d.Add "keng", -16429 150 d.Add "kong", -16427 151 d.Add "kou", -16423 152 d.Add "ku", -16419 153 d.Add "kua", -16412 154 d.Add "kuai", -16407 155 d.Add "kuan", -16403 156 d.Add "kuang", -16401 157 d.Add "kui", -16393 158 d.Add "kun", -16220 159 d.Add "kuo", -16216 160 d.Add "la", -16212 161 d.Add "lai", -16205 162 d.Add "lan", -16202 163 d.Add "lang", -16187 164 d.Add "lao", -16180 165 d.Add "le", -16171 166 d.Add "lei", -16169 167 d.Add "leng", -16158 168 d.Add "li", -16155 169 d.Add "lia", -15959 170 d.Add "lian", -15958 171 d.Add "liang", -15944 172 d.Add "liao", -15933 173 d.Add "lie", -15920 174 d.Add "lin", -15915 175 d.Add "ling", -15903 176 d.Add "liu", -15889 177 d.Add "long", -15878 178 d.Add "lou", -15707 179 d.Add "lu", -15701 180 d.Add "lv", -15681 181 d.Add "luan", -15667 182 d.Add "lue", -15661 183 d.Add "lun", -15659 184 d.Add "luo", -15652 185 d.Add "ma", -15640 186 d.Add "mai", -15631 187 d.Add "man", -15625 188 d.Add "mang", -15454 189 d.Add "mao", -15448 190 d.Add "me", -15436 191 d.Add "mei", -15435 192 d.Add "men", -15419 193 d.Add "meng", -15416 194 d.Add "mi", -15408 195 d.Add "mian", -15394 196 d.Add "miao", -15385 197 d.Add "mie", -15377 198 d.Add "min", -15375 199 d.Add "ming", -15369 200 d.Add "miu", -15363 201 d.Add "mo", -15362 202 d.Add "mou", -15183 203 d.Add "mu", -15180 204 d.Add "na", -15165 205 d.Add "nai", -15158 206 d.Add "nan", -15153 207 d.Add "nang", -15150 208 d.Add "nao", -15149 209 d.Add "ne", -15144 210 d.Add "nei", -15143 211 d.Add "nen", -15141 212 d.Add "neng", -15140 213 d.Add "ni", -15139 214 d.Add "nian", -15128 215 d.Add "niang", -15121 216 d.Add "niao", -15119 217 d.Add "nie", -15117 218 d.Add "nin", -15110 219 d.Add "ning", -15109 220 d.Add "niu", -14941 221 d.Add "nong", -14937 222 d.Add "nu", -14933 223 d.Add "nv", -14930 224 d.Add "nuan", -14929 225 d.Add "nue", -14928 226 d.Add "nuo", -14926 227 d.Add "o", -14922 228 d.Add "ou", -14921 229 d.Add "pa", -14914 230 d.Add "pai", -14908 231 d.Add "pan", -14902 232 d.Add "pang", -14894 233 d.Add "pao", -14889 234 d.Add "pei", -14882 235 d.Add "pen", -14873 236 d.Add "peng", -14871 237 d.Add "pi", -14857 238 d.Add "pian", -14678 239 d.Add "piao", -14674 240 d.Add "pie", -14670 241 d.Add "pin", -14668 242 d.Add "ping", -14663 243 d.Add "po", -14654 244 d.Add "pu", -14645 245 d.Add "qi", -14630 246 d.Add "qia", -14594 247 d.Add "qian", -14429 248 d.Add "qiang", -14407 249 d.Add "qiao", -14399 250 d.Add "qie", -14384 251 d.Add "qin", -14379 252 d.Add "qing", -14368 253 d.Add "qiong", -14355 254 d.Add "qiu", -14353 255 d.Add "qu", -14345 256 d.Add "quan", -14170 257 d.Add "que", -14159 258 d.Add "qun", -14151 259 d.Add "ran", -14149 260 d.Add "rang", -14145 261 d.Add "rao", -14140 262 d.Add "re", -14137 263 d.Add "ren", -14135 264 d.Add "reng", -14125 265 d.Add "ri", -14123 266 d.Add "rong", -14122 267 d.Add "rou", -14112 268 d.Add "ru", -14109 269 d.Add "ruan", -14099 270 d.Add "rui", -14097 271 d.Add "run", -14094 272 d.Add "ruo", -14092 273 d.Add "sa", -14090 274 d.Add "sai", -14087 275 d.Add "san", -14083 276 d.Add "sang", -13917 277 d.Add "sao", -13914 278 d.Add "se", -13910 279 d.Add "sen", -13907 280 d.Add "seng", -13906 281 d.Add "sha", -13905 282 d.Add "shai", -13896 283 d.Add "shan", -13894 284 d.Add "shang", -13878 285 d.Add "shao", -13870 286 d.Add "she", -13859 287 d.Add "shen", -13847 288 d.Add "sheng", -13831 289 d.Add "shi", -13658 290 d.Add "shou", -13611 291 d.Add "shu", -13601 292 d.Add "shua", -13406 293 d.Add "shuai", -13404 294 d.Add "shuan", -13400 295 d.Add "shuang", -13398 296 d.Add "shui", -13395 297 d.Add "shun", -13391 298 d.Add "shuo", -13387 299 d.Add "si", -13383 300 d.Add "song", -13367 301 d.Add "sou", -13359 302 d.Add "su", -13356 303 d.Add "suan", -13343 304 d.Add "sui", -13340 305 d.Add "sun", -13329 306 d.Add "suo", -13326 307 d.Add "ta", -13318 308 d.Add "tai", -13147 309 d.Add "tan", -13138 310 d.Add "tang", -13120 311 d.Add "tao", -13107 312 d.Add "te", -13096 313 d.Add "teng", -13095 314 d.Add "ti", -13091 315 d.Add "tian", -13076 316 d.Add "tiao", -13068 317 d.Add "tie", -13063 318 d.Add "ting", -13060 319 d.Add "tong", -12888 320 d.Add "tou", -12875 321 d.Add "tu", -12871 322 d.Add "tuan", -12860 323 d.Add "tui", -12858 324 d.Add "tun", -12852 325 d.Add "tuo", -12849 326 d.Add "wa", -12838 327 d.Add "wai", -12831 328 d.Add "wan", -12829 329 d.Add "wang", -12812 330 d.Add "wei", -12802 331 d.Add "wen", -12607 332 d.Add "weng", -12597 333 d.Add "wo", -12594 334 d.Add "wu", -12585 335 d.Add "xi", -12556 336 d.Add "xia", -12359 337 d.Add "xian", -12346 338 d.Add "xiang", -12320 339 d.Add "xiao", -12300 340 d.Add "xie", -12120 341 d.Add "xin", -12099 342 d.Add "xing", -12089 343 d.Add "xiong", -12074 344 d.Add "xiu", -12067 345 d.Add "xu", -12058 346 d.Add "xuan", -12039 347 d.Add "xue", -11867 348 d.Add "xun", -11861 349 d.Add "ya", -11847 350 d.Add "yan", -11831 351 d.Add "yang", -11798 352 d.Add "yao", -11781 353 d.Add "ye", -11604 354 d.Add "yi", -11589 355 d.Add "yin", -11536 356 d.Add "ying", -11358 357 d.Add "yo", -11340 358 d.Add "yong", -11339 359 d.Add "you", -11324 360 d.Add "yu", -11303 361 d.Add "yuan", -11097 362 d.Add "yue", -11077 363 d.Add "yun", -11067 364 d.Add "za", -11055 365 d.Add "zai", -11052 366 d.Add "zan", -11045 367 d.Add "zang", -11041 368 d.Add "zao", -11038 369 d.Add "ze", -11024 370 d.Add "zei", -11020 371 d.Add "zen", -11019 372 d.Add "zeng", -11018 373 d.Add "zha", -11014 374 d.Add "zhai", -10838 375 d.Add "zhan", -10832 376 d.Add "zhang", -10815 377 d.Add "zhao", -10800 378 d.Add "zhe", -10790 379 d.Add "zhen", -10780 380 d.Add "zheng", -10764 381 d.Add "zhi", -10587 382 d.Add "zhong", -10544 383 d.Add "zhou", -10533 384 d.Add "zhu", -10519 385 d.Add "zhua", -10331 386 d.Add "zhuai", -10329 387 d.Add "zhuan", -10328 388 d.Add "zhuang", -10322 389 d.Add "zhui", -10315 390 d.Add "zhun", -10309 391 d.Add "zhuo", -10307 392 d.Add "zi", -10296 393 d.Add "zong", -10281 394 d.Add "zou", -10274 395 d.Add "zu", -10270 396 d.Add "zuan", -10262 397 d.Add "zui", -10260 398 d.Add "zun", -10256 399 d.Add "zuo", -10254 400 num = Asc(sChinese) 401 a = d.Items 402 b = d.keys 403 Application.Volatile 404 For l = 1 To Len(sChinese) 405 num = Asc(Mid(sChinese, l, 1)) 406 For i = d.Count - 1 To 0 Step -1 407 If a(i) <= num Then Exit For 408 Next 409 Select Case iflag 410 Case 0 \'返回拼音(首字大写) 411 C2S = C2S & Application.WorksheetFunction.Proper(b(i)) 412 Case 1 \'返回小写拼音 413 C2S = C2S & b(i) 414 Case 2 \'返回大写拼音 415 C2S = C2S & UCase$(b(i)) 416 Case 3 \'返回小写拼音首字 417 C2S = C2S & Left$(b(i), 1) 418 Case 4 \'返回大写拼音首字 419 C2S = C2S & UCase$(Left$(b(i), 1)) 420 End Select 421 If Len(sChinese) > 1 And iflag < 3 Then C2S = C2S & " " 422 Next 423 Chinese2Spell = C2S 424 Set d = Nothing 425 End Function
View Code
参考文献2中的函数
用法：=UPPER(SUBSTITUTE(PinYin(B3),” “,””))，全部大写，去掉空格
1 Function PinYin(Hz As String) 2 Dim PinMa As String 3 Dim MyPinMa As Variant 4 Dim Temp As Integer, i As Integer, j As Integer 5 PinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292," 6 PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,ben,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19784," 7 PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng,19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,cheng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288,chuang,19281,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19242,cuan,19238,cui,19235,cun,19227,cuo,19224," 8 PinMa = PinMa & "da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,deng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773,dou,18763,du,18756,duan,18741,dui,18735,dun,18731,duo,18722," 9 PinMa = PinMa & "e,18710,en,18697,er,18696," 10 PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,18448,fou,18447,fu,18446," 11 PinMa = PinMa & "ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei,18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,guan,17961,guang,17950,gui,17947,gun,17931,guo,17928," 12 PinMa = PinMa & "ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,hei,17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676,hua,17496,huai,17487,huan,17482,huang,17468,hui,17454,hun,17433,huo,17427," 13 PinMa = PinMa & "ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,16915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,16657,jun,16647," 14 PinMa = PinMa & "ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ken,16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,kuai,16407,kuan,16403,kuang,16401,kui,16393,kun,16220,kuo,16216," 15 PinMa = PinMa & "la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,lei,16169,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,15933,lie,15920,lin,15915,ling,15903,liu,15889,long,15878,lou,15707,lu,15701,lv,15681,luan,15667,lue,15661,lun,15659,luo,15652," 16 PinMa = PinMa & "ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,15436,mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao,15385,mie,15377,min,15375,ming,15369,miu,15363,mo,15362,mou,15183,mu,15180," 17 PinMa = PinMa & "na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,nei,15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,nin,15110,ning,15109,niu,14941,nong,14937,nu,14933,nv,14930,nuan,14929,nue,14928,nuo,14926," 18 PinMa = PinMa & "o,14922,ou,14921," 19 PinMa = PinMa & "pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,pen,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,po,14654,pu,14645," 20 PinMa = PinMa & "qi,14630,qia,14594,qian,14429,qiang,14407,qiao,14399,qie,14384,qin,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151," 21 PinMa = PinMa & "ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri,14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092," 22 PinMa = PinMa & "sa,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen,13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,13859,shen,13847,sheng,13831,shi,13658,shou,13611,shu,13601,shua,13406,shuai,13404,shuan,13400,shuang,13398,shui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,13359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326," 23 PinMa = PinMa & "ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng,13095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12888,tou,12875,tu,12871,tuan,12860,tui,12858,tun,12852,tuo,12849," 24 PinMa = PinMa & "wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,12607,weng,12597,wo,12594,wu,12585," 25 PinMa = PinMa & "xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120,xin,12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861," 26 PinMa = PinMa & "ya,11847,yan,11831,yang,11798,yao,11781,ye,11604,yi,11589,yin,11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun,11067," 27 PinMa = PinMa & "za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei,11020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800,zhe,10790,zhen,10780,zheng,10764,zhi,10587,zhong,10544,zhou,10533,zhu,10519,zhua,10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,10296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,10256,zuo,10254" 28 MyPinMa = Split(PinMa, ",") 29 30 For i = 1 To Len(Hz) 31 Temp = Asc(Mid(Hz, i, 1)) 32 If Temp < 0 Then 33 Temp = Abs(Temp) 34 For j = 791 To 1 Step -2 35 If Temp <= Val(MyPinMa(j)) Then 36 PinYin = PinYin & MyPinMa(j - 1) & " " 37 Exit For 38 End If 39 Next 40 End If 41 Next 42 PinYin = Trim(PinYin) 43 End Function
View Code
版权声明：本文为xwdreamer原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。