excel：汉字拼音首字母提取

excel提取汉字的拼音首字母

excel提取汉字的拼音首字母按ALT＋F11，插入模块，在右面窗口中粘贴下面代码在某空白格中输入：=pyzm(A1) 即可Function pyzm(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integer hzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNextpyzm = pystringEnd Function。

Excel中进行用函数实现提取汉子首字母的操作技巧

Excel中进行用函数实现提取汉子首字母的操作技巧有一些场合，要用到汉字的首字母缩写，而Excel中并没有这样一个函数，可以直接提取汉字的首字母。

今天，店铺就教大家在Excel中进行用函数实现提取汉子首字母的操作技巧。

Excel中进行用函数实现提取汉子首字母的操作步骤首先，打开Excel，同时按下Alt+F11键，打开VBE界面，如下图所示。

在VBE界面中，单击图片中所示的左上角空白处，选择“插入“，”模块“。

此时，就插入了一个模块，后面的代码就要放到这个模块中，保证右侧空白区域是模块代码的编辑区(最保险的做法是双击模块)，然后执行下一步操作。

将下面的代码全部复制粘贴到空白区域。

Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20284: pinyin = "A"Case -20283 To -19776: pinyin = "B"Case -19775 To -19219: pinyin = "C"Case -19218 To -18711: pinyin = "D"Case -18710 To -18527: pinyin = "E"Case -18526 To -18240: pinyin = "F"Case -18239 To -17923: pinyin = "G"Case -17922 To -17418: pinyin = "H"Case -17417 To -16475: pinyin = "J"Case -16474 To -16213: pinyin = "K"Case -16212 To -15641: pinyin = "L"Case -15640 To -15166: pinyin = "M"Case -15165 To -14923: pinyin = "N"Case -14922 To -14915: pinyin = "O"Case -14914 To -14631: pinyin = "P"Case -14630 To -14150: pinyin = "Q"Case -14149 To -14091: pinyin = "R"Case -14090 To -13319: pinyin = "S"Case -13318 To -12839: pinyin = "T"Case -12838 To -12557: pinyin = "W"Case -12556 To -11848: pinyin = "X"Case -11847 To -11056: pinyin = "Y"Case -11055 To -2050: pinyin = "Z"Case Else: pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function然后关闭这个新界面，返回Excel，就可以像Excel自带的函数一样使用这个自定义的Getpy函数了，它只有一个参数，参数可以直接指定单元格，也可以直接输入字符。

EXCEL中怎么把汉字变成拼音,并取拼音的第一个大写字母

打开Excel->工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1"，点击在右边的空白栏中粘贴以下内容：Function pinyin(p As String) As Stringi = Asc(p)Select Case iCase -20319 To -20284: pinyin = "A"Case -20283 To -19776: pinyin = "B"Case -19775 To -19219: pinyin = "C"Case -19218 To -18711: pinyin = "D"Case -18710 To -18527: pinyin = "E"Case -18526 To -18240: pinyin = "F"Case -18239 To -17923: pinyin = "G"Case -17922 To -17418: pinyin = "H"Case -17417 To -16475: pinyin = "J"Case -16474 To -16213: pinyin = "K"Case -16212 To -15641: pinyin = "L"Case -15640 To -15166: pinyin = "M"Case -15165 To -14923: pinyin = "N"Case -14922 To -14915: pinyin = "O"Case -14914 To -14631: pinyin = "P"Case -14630 To -14150: pinyin = "Q"Case -14149 To -14091: pinyin = "R"Case -14090 To -13319: pinyin = "S"Case -13318 To -12839: pinyin = "T"Case -12838 To -12557: pinyin = "W"Case -12556 To -11848: pinyin = "X"Case -11847 To -11056: pinyin = "Y"Case -11055 To -2050: pinyin = "Z"Case Else: pinyin = pEnd SelectEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & pinyin(Mid(str, i, 1))Next iEnd Function********复制到此结束,本行不复制*******现在转换函数已编写完成!关掉此编缉的窗口。

excel：汉字拼音首字母提取

方法一：VBA自定义函数"=hztopy（）"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入下述代码，（按ALT＋F11，插入模块，在右面窗口中粘贴下面代码）那么，比如在A3中有"进退两难"，则在B3中输入"=hztopy（A3）"，则会得到"JTLN"。

不过此法有时候也会出错，比如"皓、鑫、婷、雯、奕"等，就无法提取汉字拼音首字母。

Function hztopy(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integerhzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNexthztopy = pystringEnd Function方法二：VBA自定义函数"=getpy()"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入上述代码，那么。

excel获得汉字首字母的方法

在日常工作和学习中，我们经常需要对文本进行处理和分析，尤其是对于中文文本。

在处理中文文本时，有时会遇到需要将汉字转换为拼音的需求。

而在Excel中，获得汉字的首字母则成为了一个常见的需求。

那么，如何在Excel中获得汉字的首字母呢？我们可以利用Excel内置的函数来实现这一目标。

在Excel中，可以使用VB脚本编写一个自定义函数来获取汉字的首字母。

具体步骤如下：1. 打开Excel表格，按下ALT + F11，打开VB编辑器。

2. 在VB编辑器中，插入一个新模块。

3. 在新模块中输入以下VB脚本代码：```vbFunction GetFirstLetter(str As String) As StringDim pinyin As StringDim i As IntegerDim ch As Stringpinyin = ""For i = 1 To Len(str)ch = Mid(str, i, 1)pinyin = pinyin &Left(Application.WorksheetFunction.Phonetic(ch), 1)Next iGetFirstLetter = pinyinEnd Function```4. 编译保存后，返回Excel表格界面。

5. 在需要获取汉字首字母的单元格中输入`=GetFirstLetter(A1)`，其中A1为需要转换的汉字所在的单元格。

6. 按下Enter键，即可在目标单元格中获取到相应汉字的首字母。

通过上述方法，我们可以轻松地在Excel中获得汉字的首字母，而且可以将这个函数在多个单元格中批量应用，提高工作效率。

除了使用VB脚本自定义函数外，我们还可以借助Excel内置的函数来实现汉字首字母的获取。

在Excel中，有一个叫做UNICODE的函数，可以返回指定字符的Unicode编码。

而对于汉字来说，其首字母的Unicode编码可以作为其拼音的首字母。

excel：汉字拼音首字母提取

方法一：VBA自定义函数"=hztopy（）"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入下述代码，（按ALT＋F11，插入模块，在右面窗口中粘贴下面代码）那么，比如在A3中有"进退两难"，则在B3中输入"=hztopy（A3）"，则会得到"JTLN"。

不过此法有时候也会出错，比如"皓、鑫、婷、雯、奕"等，就无法提取汉字拼音首字母。

Function hztopy(hzpy As String) As StringDim hzstring As String, pystring As StringDim hzpysum As Integer, hzi As Integer, hzpyhex As Integerhzstring = Trim(hzpy)hzpysum = Len(Trim(hzstring))pystring = ""For hzi = 1 To hzpysumhzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))Select Case hzpyhexCase &HB0A1 To &HB0C4: pystring = pystring + "A"Case &HB0C5 To &HB2C0: pystring = pystring + "B"Case &HB2C1 To &HB4ED: pystring = pystring + "C"Case &HB4EE To &HB6E9: pystring = pystring + "D"Case &HB6EA To &HB7A1: pystring = pystring + "E"Case &HB7A2 To &HB8C0: pystring = pystring + "F"Case &HB8C1 To &HB9FD: pystring = pystring + "G"Case &HB9FE To &HBBF6: pystring = pystring + "H"Case &HBBF7 To &HBFA5: pystring = pystring + "J"Case &HBFA6 To &HC0AB: pystring = pystring + "K"Case &HC0AC To &HC2E7: pystring = pystring + "L"Case &HC2E8 To &HC4C2: pystring = pystring + "M"Case &HC4C3 To &HC5B5: pystring = pystring + "N"Case &HC5B6 To &HC5BD: pystring = pystring + "O"Case &HC5BE To &HC6D9: pystring = pystring + "P"Case &HC6DA To &HC8BA: pystring = pystring + "Q"Case &HC8BB To &HC8F5: pystring = pystring + "R"Case &HC8F6 To &HCBF9: pystring = pystring + "S"Case &HCBFA To &HCDD9: pystring = pystring + "T"Case &HEDC5: pystring = pystring + "T"Case &HCDDA To &HCEF3: pystring = pystring + "W"Case &HCEF4 To &HD1B8: pystring = pystring + "X"Case &HD1B9 To &HD4D0: pystring = pystring + "Y"Case &HD4D1 To &HD7F9: pystring = pystring + "Z"Case Elsepystring = pystring + Mid(hzstring, hzi, 1)End SelectNexthztopy = pystringEnd Function方法二：VBA自定义函数"=getpy()"下面是一个VBA自定义函数，工具->宏->Visual Basic编辑器->，插入模块,在VBA编辑器里面->插入->模块->贴入上述代码，那么。

Excel怎么快速提取中文的首字母呢？

Excel怎么快速提取中⽂的⾸字母呢？右键单击Sheet1⼯作表，选中右键菜单中的查看代码。

Function Getpychar(char)temp = 65536 + Asc(char)If (temp >= 45217And temp <= 45252) ThenGetpychar = "A"ElseIf (temp >= 45253And temp <= 45760) ThenGetpychar = "B"ElseIf (temp >= 45761And temp <= 46317) ThenGetpychar = "C"ElseIf (temp >= 46318And temp <= 46825) ThenGetpychar = "D"ElseIf (temp >= 46826And temp <= 47009) ThenGetpychar = "E"ElseIf (temp >= 47010And temp <= 47296) ThenGetpychar = "F"ElseIf (temp >= 47297And temp <= 47613) ThenGetpychar = "G"ElseIf (temp >= 47614And temp <= 48118) ThenGetpychar = "H"ElseIf (temp >= 48119And temp <= 49061) ThenGetpychar = "J"ElseIf (temp >= 49062And temp <= 49323) ThenGetpychar = "K"ElseIf (temp >= 49324And temp <= 49895) ThenGetpychar = "L"ElseIf (temp >= 49896And temp <= 50370) ThenGetpychar = "M"ElseIf (temp >= 50371And temp <= 50613) ThenGetpychar = "N"ElseIf (temp >= 50614And temp <= 50621) ThenGetpychar = "O"ElseIf (temp >= 50622And temp <= 50905) ThenGetpychar = "P"ElseIf (temp >= 50906And temp <= 51386) ThenGetpychar = "Q"ElseIf (temp >= 51387And temp <= 51445) ThenGetpychar = "R"ElseIf (temp >= 51446And temp <= 52217) ThenGetpychar = "S"ElseIf (temp >= 52218And temp <= 52697) Then Getpychar = "T"ElseIf (temp >= 52698And temp <= 52979) Then Getpychar = "W"ElseIf (temp >= 52980And temp <= 53640) Then Getpychar = "X"ElseIf (temp >= 53689And temp <= 54480) Then Getpychar = "Y"ElseIf (temp >= 54481And temp <= 62289) Then Getpychar = "Z"ElseGetpychar = charEnd IfEnd FunctionFunction Getpy(str)For a = 1To Len(str)Getpy = Getpy & Getpychar(Mid(str, a, 1))Next aEnd Function函数⽤法。

Excel-VBA自定义函数取姓名的汉语拼音首字母

Excel-VBA自定义函数取姓名的汉语拼音首字母应用场景用函数取汉语拼音的首字母知识要点1：Option Compare语句，设置缺省的字符比较方法，只能在模块级使用，语法Option Compare {Binary | Text | Database} Option Compare Binary ,按排序顺序进行字符串比较A<B<C<a<b<?等，Option Compare text 不区分大小写进行字符串比较A=a2：将所有拼音首字母相同的第一个汉字取出来组成一个字符串，例如拉、洛、梁等，拉=la最靠前，取拉3：将参数中的每个字符与参照字符串中每个比较，统计参照字符串中有多少字符小于参数中的字符，如果有10个，通过chr函数，转换成第9个英文字符J，非汉字保持不变Option Compare Text '比较方式为text'声明函数PY，有一个必选参数，为特引用的单元格或者字符串Function PY(Rng As Range)Application.Volatile '声明为易失性函数Dim i As Integer, k As Integer, Str As StringIf Len(Rng) = 0 Then PY = '': Exit Function '如果引用单元格空白，则返回空文本If Not IsNumeric(Rng) Then '如果不是数值Str = WorksheetFunction.Trim(Rng) '去除空格格后，赋予变量strFor i = 1 To Len(Str) '遍历所有字符If Mid(Str, i, 1) < '啊' Then '如果小于啊，表示不是汉字PY = PY & Mid(Str, i, 1) '保留原字符不变Elsek = 1'将字符串中每个字符与参数中的字符进行比较，统计字符串中大于参数中的字符的个数，得到变量 kDo Until Mid$('八嚓哒妸发旮铪讥讥咔垃妈拿哦妑七然仨他哇哇哇夕丫匝咗', k, 1) > Mid$(Str, i, 1)k = k 1LoopPY = PY & Chr(64 k) '根据K值得到大写的英文字母(chr(65)为第一个字母AEnd IfNextEnd IfEnd Function。