Alphanumeric to Numeric

dleitch70

New Member
Joined
Apr 18, 2008
Messages
43
I'm looking for help with converting an alphanumeric number (ex. ug7v899j) to a numeric number? I would appreciate any suggestions. So far I've not been able to find anything on the forum.
 
For the phone number keypad conversion, you can create you own function in VBA to do it, like this:
VBA Code:
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
Then just use it like any other Excel function.
So, if your entry was in cell A5, just use this formula:
=ConvertValue(A5)
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
If you have Excel 365, that will be easy to do:
工作簿4
ABCD
1ug7v899j 21772289910
2
3ug7v899j should convert to 2172289910
4
Sheet1
Cell Formulas
RangeFormula
B1B1=CONCAT(IFERROR(MATCH(MID(A1,ROW($1:$8),1),CHAR(ROW($65:$90)),),MID(A1,ROW($1:$8),1)))
Awesome! That works great. One question though, what is the $65:$90 for?
 
Upvote 0
$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}
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top