提取名字首字母代码 提取名字首字母代码怎么写

问题没有说清楚。如果是提取个字，用left函数。设名在A列，要在B列显示名的个字，就在B1中输入=left(A1)，然后公式填充，就能在B列得到A列的名的个字或字母。

提取名字首字母代码 提取名字首字母代码怎么写

提取名字首字母代码 提取名字首字母代码怎么写

用VBA来处理。

也可建立辅助列来达到你的要求。前提是你的品名称长度都大于1哈，

插入b列，在B2列写公式 =if(len(a2)=1,a2,b1)，填充，

用Vlookup公式就可以获取你想要的结果了，

下面是一个VBA自定义函数，按ALT＋F11，插入模块，在右面窗口中粘贴下面代码。

若B10内容为 航天6号a，则在某空白格中输入：=hztopy(B10) 即可

Function hztopy(hzpy As String) As String

Dim hzstring As String, pystring As String

Dim hzpysum As Integer, hzi As Integer, hzpyhex As Integer

hzstring = Trim(hzpy)

hzpysum = Len(Trim(hzstring))

pystring = ""

For hzi = 1 To hzpysum

hzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 1)))

Select Case hzpyhex

Case &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 Else

pystring = pystring + Mid(hzstring, hzi, 1)

End Select

Next

hztopy = pystring

End Function

若 A1单元格为 ZHANGSAN =MID(A1,1,1) Z

思路：

1 汉字转拼音

2 提取首字母。

用自定义函数

有谁知道Excel中提取拼音的首字母的方法？

你好，根据你的描述，你的问题可以由宏代码实现，当然也可以由函数公式直接实现，不过我觉得用函数公式实现更为简单。如姓名在a1单元格，姓名为蒋彦斌三个字的，在任意单元格输入以下公式，会得出JYB的结果。公式如下：

=LOOKUP(CODE(LEFT(A1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(RIGHT(A1,2)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(RIGHT(A1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

以上公式给你备用，难说你以后又要用，算是给你备用吧！

但是根据你的题目要求，你并不需要所有汉字的首字母，比如你的C1是“机械”，那么B1需要为“J”；C2是“玩具”，那么B2需要为“W”。看来你的你只要个汉字的首字母，其他的都不要。只要则删除上面公式中&后面的部分即可。具体公式是（把以下公式粘贴入B1单元格，用填充柄往下拖公式即可）：

=LOOKUP(CODE(LEFT(C1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

如下图：

真诚希望能够帮助到你！当然上面有人已经用邮件和百度HI帮你解决。我不知道他是不是用我一样的方法，但我觉得，邮件其他人看不到，能够解决问题的应该共享，这样百度网络才会更美好，大家也才需要网络！－－－－－河阳小子！

在日常工作中，为了方便查询、排序或者编码，需要生成汉字内容信息的首字母，如果信息很多的话，一个个录入是一件很麻烦的事情。因此，关于在EXCEL中提取拼音的首字母的方法，具体的作如下：

一、新建Excel文件

新建一个Excel文件，输入示例汉字内容。

二、设置【开发工具】菜单

【文件】->【选项】->【自定义功能区】->【主选项卡】->勾选【开发工具】

点击确定以后，导航菜单里增加一项【开发工具】

三、自定义函数

打开【开发工具】菜单，点击个【Visual Basic】,弹出代码窗口，

在窗口左侧空白处，点击右键，选择【插入】->【模块】。窗口右侧出现一个白色背景框。

粘贴如下内容到

点击菜单中【保存】，弹出一个提示框，点【是】，再次弹出一个提示框，点【确定】。

三、调用自定义函数

关闭代码窗口，回到Excel文件。鼠标定位到“拼音首字母”列行（C2单元格）。

在函数框中输入=getpy(B2)，（调用自定义函数，获取B2单元格中汉字内容首字母）回车，可以看到获取到B2单元格中内容的首字母。

四、生成首字母

鼠标定位到单元格C2右下角，光标变成黑色十字，按住鼠标左键往下拉至C15行。获取全部内容的首字母。

可以网上搜一下啊，很多的

1.要保证拼音前面没有空格 可以用函数 trim()取出

2.然后在B1中输入 =left(C1,1)

下拉即可

C列的文字的个是否是字母？如果是则用B1=left(C1,1)即可；如果前面还有其他元素，则应先判断字母出现的位置再用B1=MID(C1,n,1)提取.

参见：Excel汉字转拼音的完美解决方案～函数进化（地址见参考资料）

1、把其中的hztopy类和zhtopy函数导入到工作表，就可以使用hztopy函数，或者把数据过去直接在上面改

2、输入如下公式，向下填充

=left(hztopy(LEFT(c2,1)),1)

能 看下表格吗？

提取数据一般用MID和LEFT函数！

能看到表格 ！在B1输入：=LEFT(C1,1) 下拉填充

=MID(B2,1,1)下拉填充

分两步

1、取得拼音

得用到VBA

打开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

按ALT+F11回到EXCEL

2、如果你的表如下

A B C D

1 姓名 出生年月 工作时间

2 刘德华 1965 1982

在D2单元格输入公式

=getpy(A2)&right(B2,2)&right(C2,2)

OK了

楼上 用函数的方法， 很强大 自愧不如

我用一个笨办法 ，楼主有兴趣的话参考一下吧：

“选中-—数据/排序—字母排序 ”（如图）

再手动 选添一下人名&字母吧 方法很笨 但没有函数基础的可以参考一下！

LOOKUP(CODE(LEFT(B1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(B1,2,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(B1,3,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

公式太长，不写if了，名字为2个字的删除一个&lookup()

好像公式里就有这样的函数

按ALT+F11进入VBA

插入→模块，插入一个新模块。再双击插入的模块，进入模块代码编辑状态

以下的代码

Function pinyin(p As String) As String

i = Asc(p)

Select Case i

Case -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 -145: pinyin = "O"

Case -144 To -14631: pinyin = "P"

Case -14630 To -14150: pinyin = "Q"

Case -14149 To -140: 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 = p

End Select

End Function

Function getpy(str)

For i = 1 To Len(str)

getpy = getpy & pinyin(Mid(str, i, 1))

Next i

End Function

代码输入完成后，关闭Visual Basic编辑窗口，返回Excel编辑状态

到这，自定义函数就设置好了

然后在你想要提取首字母的地方，输入公式=getpy（）

比如

这样