Excel中使用VBS自定义函数将中文转为拼音首字母

1、在“开发工具”中，点击“Visual Basic”。如果没有“开发工具”，则添加。



2、添加“模块”，在窗口中添加自定义函数。

Function MyGetPYChar(char)
MyCodeNumber = 65536 + Asc(char)
If (MyCodeNumber >= 45217 And MyCodeNumber <= 45252) Then
MyGetPYChar = “A”
ElseIf (MyCodeNumber >= 45253 And MyCodeNumber <= 45760) Then
MyGetPYChar = “B”
ElseIf (MyCodeNumber >= 45761 And MyCodeNumber <= 46317) Then
MyGetPYChar = “C”
ElseIf (MyCodeNumber >= 46318 And MyCodeNumber <= 46825) Then
MyGetPYChar = “D”
ElseIf (MyCodeNumber >= 46826 And MyCodeNumber <= 47009) Then
MyGetPYChar = “E”
ElseIf (MyCodeNumber >= 47010 And MyCodeNumber <= 47296) Then
MyGetPYChar = “F”
ElseIf (MyCodeNumber >= 47297 And MyCodeNumber <= 47613) Then
MyGetPYChar = “G”
ElseIf (MyCodeNumber >= 47614 And MyCodeNumber <= 48118) Then
MyGetPYChar = “H”
ElseIf (MyCodeNumber >= 48119 And MyCodeNumber <= 49061) Then
MyGetPYChar = “J”
ElseIf (MyCodeNumber >= 49062 And MyCodeNumber <= 49323) Then
MyGetPYChar = “K”
ElseIf (MyCodeNumber >= 49324 And MyCodeNumber <= 49895) Then
MyGetPYChar = “L”
ElseIf (MyCodeNumber >= 49896 And MyCodeNumber <= 50370) Then
MyGetPYChar = “M”
ElseIf (MyCodeNumber >= 50371 And MyCodeNumber <= 50613) Then
MyGetPYChar = “N”
ElseIf (MyCodeNumber >= 50614 And MyCodeNumber <= 50621) Then
MyGetPYChar = “O”
ElseIf (MyCodeNumber >= 50622 And MyCodeNumber <= 50905) Then
MyGetPYChar = “P”
ElseIf (MyCodeNumber >= 50906 And MyCodeNumber <= 51386) Then
MyGetPYChar = “Q”
ElseIf (MyCodeNumber >= 51387 And MyCodeNumber <= 51445) Then
MyGetPYChar = “R”
ElseIf (MyCodeNumber >= 51446 And MyCodeNumber <= 52217) Then
MyGetPYChar = “S”
ElseIf (MyCodeNumber >= 52218 And MyCodeNumber <= 52697) Then
MyGetPYChar = “T”
ElseIf (MyCodeNumber >= 52698 And MyCodeNumber <= 52979) Then
MyGetPYChar = “W”
ElseIf (MyCodeNumber >= 52980 And MyCodeNumber <= 53640) Then
MyGetPYChar = “X”
ElseIf (MyCodeNumber >= 53689 And MyCodeNumber <= 54480) Then
MyGetPYChar = “Y”
ElseIf (MyCodeNumber >= 54481 And MyCodeNumber <= 62289) Then
MyGetPYChar = “Z”
Else '如果不是中文，则不处理
MyGetPYChar = char
End If
End Function

Function MyGetPY(str)
For i = 1 To Len(str)
MyGetPY = MyGetPY & MyGetPYChar(Mid(str, i, 1))
Next i
End Function

3、引用自定义函数



4、转换效果

5、如果非本地Excel文件，可能受限，如下图处理