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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dleitch70

New Member
Joined
Apr 18, 2008
Messages
43
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?
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
$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:

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
Got it! Thanks!
you are welcom :giggle: Pleae update you profile about the excel version you are using at your convenient, that will help us make more efficient formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,317
Messages
5,641,491
Members
417,212
Latest member
rsturbox

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
Top