Option Explicit
Function PY(TT As String) As Variant \'自定义函数，目的：把一组汉字变为一组汉字拼音的第一个字母。
Dim i%, temp$
PY = ""
For i = 1 To Len(TT)
temp = Asc(Mid$(TT, i, 1))
If temp > 255 Or temp < 0 Then \'是汉字吗？
PY = PY & pinyin(Mid$(TT, i, 1)) \'转化为拼音首字母，
Else
PY = PY & LCase(Mid$(TT, i, 1)) \'转化英文字母
End If
Next i
End Function
Function pinyin(myStr As String) As Variant \'自定义函数，目的：把单个汉字变为拼音的第一个字母。
On Error Resume Next
myStr = StrConv(myStr, vbNarrow)
If Asc(myStr) > 0 Or Err.Number = 1004 Then pinyin = ""
pinyin = Application.WorksheetFunction.VLookup(myStr, [{"啊","a";"八","b";"嚓","c";"大","d";"额","e";"发","f";"个","g";"哈","h";"机","j";"咔","k";"垃","l";"米","m";"你","n";"噢","o";"皮","p";"七","q";"人","r";"仨","s";"他","t";"我","w";"西","x";"牙","y";"杂","z"}], 2)
End Function
使用方法是：打开excel文档，按下ALT+F11，打开VBE编辑器，然后插入——模块，复制上面的代码，关闭VBE。回到工作表中，输入公式，比如：=py(A2)，就可以提取A2单元格汉字拼音首字母。
出自：http://www.ittribalwo.com/article/1542.html