Function ConvertValue(str As String) As String
Dim i As Long
Dim temp As String
Dim ch As Variant
If Len(str) > 0 Then
For i = 1 To Len(str)
ch = Mid(str, i, 1)
If IsNumeric(ch) Then
temp = temp & ch
Else
Select Case LCase(ch)
Case "a", "b", "c"
temp = temp & 2
Case "d", "e", "f"
temp = temp & 3
Case "g", "h", "i"
temp = temp & 4
Case "j", "k", "l"
temp = temp & 5
Case "m", "n", "o"
temp = temp & 6
Case "p", "q", "r", "s"
temp = temp & 7
Case "t", "u", "v"
temp = temp & 8
Case "w", "x", "y", "z"
temp = temp & 9
End Select
End If
Next i
End If
ConvertValue = temp
End Function
Awesome! That works great. One question though, what is the $65:$90 for?If you have Excel 365, that will be easy to do:
工作簿4
A B C D 1 ug7v899j 21772289910 2 3 ug7v899j should convert to 2172289910 4 Sheet1
Cell Formulas Range Formula B1 B1 =CONCAT(IFERROR(MATCH(MID(A1,ROW($1:$8),1),CHAR(ROW($65:$90)),),MID(A1,ROW($1:$8),1)))
Got it! Thanks!$65:$90 are row numbers, row($65:$90) retunrs an array of {65;66;67;68.......;89;90}.Because the code of a is 65, so char(row(65:90)) will return {a;b;c;....;y;z}