Excel中文转拼音，要不要空格可自选

转载

huangbao_007 2018-11-30 21:21:23 博主文章分类：System_Windows

http://blog.163.com/huangbao_007/blog/static/35636535201271725449146/

下载LOFTER我的照片书 |

Excel中文转拼音（简称版）

打开Excel->工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1"，粘贴以下内容：

Function getpychar(char) tmp = 65536 + Asc(char) If (tmp >= 45217 And tmp <= 45252) Then getpychar = "A" ElseIf (tmp >= 45253 And tmp <= 45760) Then getpychar = "B" ElseIf (tmp >= 45761 And tmp <= 46317) Then getpychar = "C" ElseIf (tmp >= 46318 And tmp <= 46825) Then getpychar = "D" ElseIf (tmp >= 46826 And tmp <= 47009) Then getpychar = "E" ElseIf (tmp >= 47010 And tmp <= 47296) Then getpychar = "F" ElseIf (tmp >= 47297 And tmp <= 47613) Then getpychar = "G" ElseIf (tmp >= 47614 And tmp <= 48118) Then getpychar = "H" ElseIf (tmp >= 48119 And tmp <= 49061) Then getpychar = "J" ElseIf (tmp >= 49062 And tmp <= 49323) Then getpychar = "K" ElseIf (tmp >= 49324 And tmp <= 49895) Then getpychar = "L" ElseIf (tmp >= 49896 And tmp <= 50370) Then getpychar = "M" ElseIf (tmp >= 50371 And tmp <= 50613) Then getpychar = "N" ElseIf (tmp >= 50614 And tmp <= 50621) Then getpychar = "O" ElseIf (tmp >= 50622 And tmp <= 50905) Then getpychar = "P" ElseIf (tmp >= 50906 And tmp <= 51386) Then getpychar = "Q" ElseIf (tmp >= 51387 And tmp <= 51445) Then getpychar = "R" ElseIf (tmp >= 51446 And tmp <= 52217) Then getpychar = "S" ElseIf (tmp >= 52218 And tmp <= 52697) Then getpychar = "T" ElseIf (tmp >= 52698 And tmp <= 52979) Then getpychar = "W" ElseIf (tmp >= 52980 And tmp <= 53640) Then getpychar = "X" ElseIf (tmp >= 53689 And tmp <= 54480) Then getpychar = "Y" ElseIf (tmp >= 54481 And tmp <= 62289) Then getpychar = "Z" Else '如果不是中文，则不处理 getpychar = 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中使用，方法如下： A1 A2 中国 =getpy(A1)

============================================================ Excel中文转拼音（完整版）

打开Excel->工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块2"，点击在右边的空白栏中粘贴以下内容：

Option Explicit Option Compare Text

Public Function HZQP(expression As String, Optional delimiter As String = " ", Optional limit As Integer = -1) As String

'expression ' 必需的?包含要转换的字符串? 'delimiter ' 可选的。用于标识分隔拼音的字符串字符。如果忽略，则使用空格字符(" ")作为分隔符。如果

'delimiter是一个长度为零的字符串，则返回仅包含一个元素，即完整的 expression字符串。 'limit ' 可选的。要返回拼音字符数，–1表示返回所有的拼音字符数，1表示返回拼音首字母。

Dim STR As String, arrWord(400) As String Dim i As Integer, j As Integer arrWord(1) = "吖a" arrWord(2) = "哎ai" arrWord(3) = "腤an" arrWord(4) = "肮ang" arrWord(5) = "凹ao" arrWord(6) = "八ba" arrWord(7) = "挀bai" arrWord(8) = "扳ban" arrWord(9) = "邦bang" arrWord(10) = "勹bao" arrWord(11) = "陂bei" arrWord(12) = "奔ben" arrWord(13) = "崩beng" arrWord(14) = "皀bi" arrWord(15) = "边bian" arrWord(16) = "杓biao" arrWord(17) = "憋bie" arrWord(18) = "邠bin" arrWord(19) = "仌bing" arrWord(20) = "拨bo" arrWord(21) = "峬bu" arrWord(22) = "嚓ca" arrWord(23) = "猜cai" arrWord(24) = "飡can" arrWord(25) = "仓cang" arrWord(26) = "撡cao" arrWord(27) = "冊ce" arrWord(28) = "嵾cen" arrWord(29) = "噌ceng" arrWord(30) = "*cha" arrWord(31) = "拆chai" arrWord(32) = "辿chan" arrWord(33) = "伥chang" arrWord(34) = "抄chao" arrWord(35) = "车che" arrWord(36) = "抻chen" arrWord(37) = "阷cheng" arrWord(38) = "吃chi" arrWord(39) = "充chong" arrWord(40) = "抽chou" arrWord(41) = "出chu" arrWord(42) = "搋chuai" arrWord(43) = "巛chuan" arrWord(44) = "刅chuang" arrWord(45) = "吹chui" arrWord(46) = "旾chun" arrWord(47) = "踔chuo" arrWord(48) = "呲ci" arrWord(49) = "从cong" arrWord(50) = "凑cou" arrWord(51) = "粗cu" arrWord(52) = "汆cuan" arrWord(53) = "崔cui" arrWord(54) = "邨cun" arrWord(55) = "搓cuo" arrWord(56) = "咑da" arrWord(57) = "呆dai" arrWord(58) = "丹dan" arrWord(59) = "当dang" arrWord(60) = "刀dao" arrWord(61) = "恴de" arrWord(62) = "灯deng" arrWord(63) = "仾di" arrWord(64) = "敁dian" arrWord(65) = "刁diao" arrWord(66) = "爹die" arrWord(67) = "丁ding" arrWord(68) = "丟diu" arrWord(69) = "东dong" arrWord(70) = "剅dou" arrWord(71) = "嘟du" arrWord(72) = "耑duan" arrWord(73) = "垖dui" arrWord(74) = "吨dun" arrWord(75) = "多duo" arrWord(76) = "妸e" arrWord(77) = "奀en" arrWord(78) = "儿er" arrWord(79) = "发fa" arrWord(80) = "帆fan" arrWord(81) = "方fang" arrWord(82) = "飞fei" arrWord(83) = "分fen" arrWord(84) = "丰feng" arrWord(85) = "仏fo" arrWord(86) = "紑fou" arrWord(87) = "夫fu" arrWord(88) = "旮ga" arrWord(89) = "该gai" arrWord(90) = "干gan" arrWord(91) = "冈gang" arrWord(92) = "皋gao" arrWord(93) = "戈ge" arrWord(94) = "给gei" arrWord(95) = "根gen" arrWord(96) = "更geng" arrWord(97) = "工gong" arrWord(98) = "勾gou" arrWord(99) = "估gu" arrWord(100) = "瓜gua" arrWord(101) = "乖guai" arrWord(102) = "关guan" arrWord(103) = "光guang" arrWord(104) = "归gui" arrWord(105) = "衮gun" arrWord(106) = "呙guo" arrWord(107) = "铪ha" arrWord(108) = "嗨hai" arrWord(109) = "佄han" arrWord(110) = "夯hang" arrWord(111) = "蒿hao" arrWord(112) = "诃he" arrWord(113) = "黒hei" arrWord(114) = "拫hen" arrWord(115) = "亨heng" arrWord(116) = "叿hong" arrWord(117) = "侯hou" arrWord(118) = "乎hu" arrWord(119) = "花hua" arrWord(120) = "怀huai" arrWord(121) = "欢huan" arrWord(122) = "巟huang" arrWord(123) = "灰hui" arrWord(124) = "昏hun" arrWord(125) = "吙huo" arrWord(126) = "丌ji" arrWord(127) = "加jia" arrWord(128) = "戋jian" arrWord(129) = "江jiang" arrWord(130) = "艽jiao" arrWord(131) = "阶jie" arrWord(132) = "巾jin" arrWord(133) = "坕jing" arrWord(134) = "冂jiong" arrWord(135) = "丩jiu" arrWord(136) = "凥ju" arrWord(137) = "姢juan" arrWord(138) = "噘jue" arrWord(139) = "军jun" arrWord(140) = "咔ka" arrWord(141) = "开kai" arrWord(142) = "刊kan" arrWord(143) = "忼kang" arrWord(144) = "*kao" arrWord(145) = "匼ke" arrWord(146) = "肎ken" arrWord(147) = "劥keng" arrWord(148) = "空kong" arrWord(149) = "抠kou" arrWord(150) = "郀ku" arrWord(151) = "夸kua" arrWord(152) = "蒯kuai" arrWord(153) = "宽kuan" arrWord(154) = "匡kuang" arrWord(155) = "亏kui" arrWord(156) = "坤kun" arrWord(157) = "扩kuo" arrWord(158) = "垃la" arrWord(159) = "来lai" arrWord(160) = "兰lan" arrWord(161) = "啷lang" arrWord(162) = "捞lao" arrWord(163) = "仂le" arrWord(164) = "雷lei" arrWord(165) = "塄leng" arrWord(166) = "唎li" arrWord(167) = "俩lia" arrWord(168) = "嫾lian" arrWord(169) = "簗liang" arrWord(170) = "蹽liao" arrWord(171) = "咧lie" arrWord(172) = "邻lin" arrWord(173) = "伶ling" arrWord(174) = "溜liu" arrWord(175) = "囖lo" arrWord(176) = "龙long" arrWord(177) = "娄lou" arrWord(178) = "露lu" arrWord(179) = "驴lv" arrWord(180) = "孪luan" arrWord(181) = "掠lue" arrWord(182) = "抡lun" arrWord(183) = "頱luo" arrWord(184) = "妈ma" arrWord(185) = "埋mai" arrWord(186) = "颟man" arrWord(187) = "牤mang" arrWord(188) = "猫mao" arrWord(189) = "庅me" arrWord(190) = "沒mei" arrWord(191) = "椚men" arrWord(192) = "掹meng" arrWord(193) = "咪mi" arrWord(194) = "芇mian" arrWord(195) = "喵miao" arrWord(196) = "乜mie" arrWord(197) = "民min" arrWord(198) = "名ming" arrWord(199) = "谬miu" arrWord(200) = "摸mo" arrWord(201) = "哞mou" arrWord(202) = "母mu" arrWord(203) = "拏na" arrWord(204) = "腉nai" arrWord(205) = "囡nan" arrWord(206) = "囔nang" arrWord(207) = "孬nao" arrWord(208) = "讷ne" arrWord(209) = "馁nei" arrWord(210) = "嫩nen" arrWord(211) = "能neng" arrWord(212) = "妮ni" arrWord(213) = "拈nian" arrWord(214) = "娘niang" arrWord(215) = "鸟niao" arrWord(216) = "捏nie" arrWord(217) = "脌nin" arrWord(218) = "宁ning" arrWord(219) = "妞niu" arrWord(220) = "农nong" arrWord(221) = "奴nu" arrWord(222) = "女nv" arrWord(223) = "疟nue" arrWord(224) = "奻nuan" arrWord(225) = "郍nuo" arrWord(226) = "噢o" arrWord(227) = "讴ou" arrWord(228) = "妑pa" arrWord(229) = "拍pai" arrWord(230) = "眅pan" arrWord(231) = "乓pang" arrWord(232) = "抛pao" arrWord(233) = "呸pei" arrWord(234) = "喷pen" arrWord(235) = "匉peng" arrWord(236) = "丕pi" arrWord(237) = "片pian" arrWord(238) = "剽piao" arrWord(239) = "氕pie" arrWord(240) = "姘pin" arrWord(241) = "娉ping" arrWord(242) = "钋po" arrWord(243) = "仆pu" arrWord(244) = "七qi" arrWord(245) = "掐qia" arrWord(246) = "千qian" arrWord(247) = "呛qiang" arrWord(248) = "悄qiao" arrWord(249) = "切qie" arrWord(250) = "亲qin" arrWord(251) = "靑qing" arrWord(252) = "宆qiong" arrWord(253) = "丘qiu" arrWord(254) = "区qu" arrWord(255) = "峑quan" arrWord(256) = "炔que" arrWord(257) = "夋qun" arrWord(258) = "呥ran" arrWord(259) = "穣rang" arrWord(260) = "荛rao" arrWord(261) = "惹re" arrWord(262) = "人ren" arrWord(263) = "扔reng" arrWord(264) = "日ri" arrWord(265) = "戎rong" arrWord(266) = "厹rou" arrWord(267) = "嶿ru" arrWord(268) = "堧ruan" arrWord(269) = "桵rui" arrWord(270) = "闰run" arrWord(271) = "叒ruo" arrWord(272) = "仨sa" arrWord(273) = "毢sai" arrWord(274) = "三san" arrWord(275) = "桒sang" arrWord(276) = "掻sao" arrWord(277) = "色se" arrWord(278) = "森sen" arrWord(279) = "僧seng" arrWord(280) = "杀sha" arrWord(281) = "筛shai" arrWord(282) = "山shan" arrWord(283) = "伤shang" arrWord(284) = "弰shao" arrWord(285) = "奢she" arrWord(286) = "申shen" arrWord(287) = "升sheng" arrWord(288) = "尸shi" arrWord(289) = "収shou" arrWord(290) = "书shu" arrWord(291) = "刷shua" arrWord(292) = "衰shuai" arrWord(293) = "闩shuan" arrWord(294) = "霜shuang" arrWord(295) = "谁shui" arrWord(296) = "吮shun" arrWord(297) = "说shuo" arrWord(298) = "厶si" arrWord(299) = "忪song" arrWord(300) = "凁sou" arrWord(301) = "苏su" arrWord(302) = "狻suan" arrWord(303) = "夊sui" arrWord(304) = "孙sun" arrWord(305) = "唆suo" arrWord(306) = "他ta" arrWord(307) = "囼tai" arrWord(308) = "坍tan" arrWord(309) = "汤tang" arrWord(310) = "仐tao" arrWord(311) = "忑te" arrWord(312) = "鼟teng" arrWord(313) = "剔ti" arrWord(314) = "天tian" arrWord(315) = "旫tiao" arrWord(316) = "贴tie" arrWord(317) = "厅ting" arrWord(318) = "囲tong" arrWord(319) = "偷tou" arrWord(320) = "凸tu" arrWord(321) = "湍tuan" arrWord(322) = "推tui" arrWord(323) = "吞tun" arrWord(324) = "乇tuo" arrWord(325) = "屲wa" arrWord(326) = "歪wai" arrWord(327) = "弯wan" arrWord(328) = "尩wang" arrWord(329) = "危wei" arrWord(330) = "昷wen" arrWord(331) = "翁weng" arrWord(332) = "挝wo" arrWord(333) = "乌wu" arrWord(334) = "夕xi" arrWord(335) = "呷xia" arrWord(336) = "仙xian" arrWord(337) = "乡xiang" arrWord(338) = "灱xiao" arrWord(339) = "些xie" arrWord(340) = "心xin" arrWord(341) = "兴xing" arrWord(342) = "凶xiong" arrWord(343) = "休xiu" arrWord(344) = "戌xu" arrWord(345) = "吅xuan" arrWord(346) = "疶xue" arrWord(347) = "坃xun" arrWord(348) = "丫ya" arrWord(349) = "咽yan" arrWord(350) = "殃yang" arrWord(351) = "邀yao" arrWord(352) = "椰ye" arrWord(353) = "一yi" arrWord(354) = "乚yin" arrWord(355) = "应ying" arrWord(356) = "哟yo" arrWord(357) = "佣yong" arrWord(358) = "优you" arrWord(359) = "扜yu" arrWord(360) = "囦yuan" arrWord(361) = "曰yue" arrWord(362) = "蒀yun" arrWord(363) = "帀za" arrWord(364) = "災zai" arrWord(365) = "兂zan" arrWord(366) = "牂zang" arrWord(367) = "傮zao" arrWord(368) = "啫ze" arrWord(369) = "贼zei" arrWord(370) = "怎zen" arrWord(371) = "曽zeng" arrWord(372) = "吒zha" arrWord(373) = "夈zhai" arrWord(374) = "沾zhan" arrWord(375) = "张zhang" arrWord(376) = "佋zhao" arrWord(377) = "蜇zhe" arrWord(378) = "贞zhen" arrWord(379) = "凧zheng" arrWord(380) = "之zhi" arrWord(381) = "中zhong" arrWord(382) = "州zhou" arrWord(383) = "朱zhu" arrWord(384) = "抓zhua" arrWord(385) = "拽zhuai" arrWord(386) = "专zhuan" arrWord(387) = "妆zhuang" arrWord(388) = "隹zhui" arrWord(389) = "宒zhun" arrWord(390) = "卓zhuo" arrWord(391) = "仔zi" arrWord(392) = "宗zong" arrWord(393) = "邹zou" arrWord(394) = "租zu" arrWord(395) = "劗zuan" arrWord(396) = "厜zui" arrWord(397) = "尊zun" arrWord(398) = "昨zuo" arrWord(399) = "咗zuo" For i = 1 To Len(expression) STR = Mid(expression, i, 1) If (Asc(STR) < 0) Then For j = 1 To 399 If Left(arrWord(j), 1) > STR Then If limit = -1 Then STR = Mid(arrWord(j - 1), 2) & delimiter Else STR = Mid(arrWord(j - 1), 2, limit) & delimiter End If Exit For End If Next End If HZQP = HZQP + STR Next HZQP = Left(HZQP, Len(HZQP) - Len(delimiter)) End Function

三种用法: =hzqp(A2,"",-1) =hzqp(A2,"-",-1) =hzqp(A2," ",-1) 我们 women wo-men wo men