# Alphanumeric to Numeric

#### dleitch70

##### New Member
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
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
If you have Excel 365, that will be easy to do:

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
\$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:

#### dleitch70

##### New Member
\$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}
Got it! Thanks!

#### shaowu459

##### Well-known Member
Got it! Thanks!
you are welcom Pleae update you profile about the excel version you are using at your convenient, that will help us make more efficient formulas.

Replies
7
Views
122
Replies
13
Views
139
Replies
4
Views
61
Replies
5
Views
62
Replies
5
Views
63

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

### 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