VBA: 字母转为数字
文章背景：在工作中，有时需要将字母转为数字字符串，比如将A转为00001，B转为00002。下面通过VBA编写将字母（A~XFD）转为数字的函数。
Function LetterToNum(ByVal letter As String) As String
'Convert letter(A-XDF) to numerical string, like A to 00001, B to 00002 ...
Dim temp As String, number As Integer
If IsAlpha(letter) Then
'Capital letter
temp = UCase(letter)
'First letter
number = Asc(Left(temp, 1)) - Asc("A") + 1
'Second letter
If Len(letter) >= 2 Then
number = number * 26 + Asc(Mid(temp, 2, 1)) - Asc("A") + 1
'Third letter
If Len(letter) >= 3 Then
number = number * 26 + Asc(Mid(temp, 3, 1)) - Asc("A") + 1
If Len(letter) >= 4 Then
LetterToNum = "Letter more than three"
Exit Function
End If
End If
End If
LetterToNum = Format(number, "00000")
'非字符串
Else
LetterToNum = "Not a string"
End If
Exit Function
End Function
Private Function IsAlpha(s As String) As Boolean
'Check if a string only contains letters
IsAlpha = Len(s) And Not s Like "*[!a-zA-Z]*"
End Function
（1）上述函数实现的功能是将字母（A to XFD）转化为相应的数值型字符串，比如将A转为00001，B转为00002。
（2）Asc函数：Returns an Integer representing the character code corresponding to the first letter in a string.
参考资料：
[1] vba 字母列转为数字列(https://zhidao.baidu.com/question/624292971619231564.html?qbpn=2_2&tx=&word=vba%20%E5%AD%97%E6%AF%8D%E5%88%97%E8%BD%AC%E4%B8%BA%E6%95%B0%E5%AD%97%E5%88%97&fr=newsearchlist)
[2] excel vba判断字符串是否只包含字母(https://www.learnfk.com/code-examples/vb/excel-vba-check-if-a-string-only-contains-letters.html)
[3] Asc function(https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/asc-function)
[4] Format function(https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications)
腾讯云开发者
