首页 > 宏-汉字转换拼音

宏-汉字转换拼音

发布时间：2015-01-23 11:31:56 来源：文档文库

小中大

字号：

手机查看

公司要建DataBase，为了让老外们看明白，所以要吧中文店名全部转换成拼音。

5000多家，搞得头好晕，所以请教了下高手，发现了VBA的一个小技巧，分享给大家，希望大家有用哦::P

具体方法如下：

打开Excel->Tools->Macro(宏)->Viaual Basic编辑器

点击插入->Module(模块)

然后会出现一个Module1，空白栏中粘贴以下内容：

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

到上面end就粘帖完毕了，然后关掉VB编辑窗口，就回到了excel里

然后在一列里输入文字，我们有3种公式可以实现方法

三种用法: =hzqp(A2,"",-1)       =hzqp(A2,"-",-1)       =hzqp(A2," ",-1)

蒙自西路       mengzixilu       meng-zi-xi-lu          meng zi xi lu

另外附上已经做好的模板