Convert Alpha to Phone keypad numeric

[yram]

Is there an easy excel formula that I can use to create id numbers for a list of students using the first initial of their first name and the first four digits of their last name? I'd like to take those five letters and convert them to the corresponding numbers on a telephone keypad.

For example, I need a formula that would take John Smith, select JSMIT, and convert it to 57648.

Thanks!

 

[Joe4]

Welcome to the Board!

You can create a User Defined Function to do this (UDF). Just place this code in a Standard Module in the VB Editor and then use it like any other Excel function.

Here is the code:

Function PhoneKeyPadConverter(myEntry As String) As String
 
    Dim myLen As Long
    Dim i As Long
    Dim myNum As String
    
'   Find length of value to convert
    myLen = Len(myEntry)
    
'   Loop through entry, converting value
    If myLen > 0 Then
        For i = 1 To myLen
            Select Case UCase(Mid(myEntry, i, 1))
                Case "A", "B", "C"
                    myNum = "2"
                Case "D", "E", "F"
                    myNum = "3"
                Case "G", "H", "I"
                    myNum = "4"
                Case "J", "K", "L"
                    myNum = "5"
                Case "M", "N", "O"
                    myNum = "6"
                Case "P", "Q", "R", "S"
                    myNum = "7"
                Case "T", "U", "V"
                    myNum = "8"
                Case "W", "X", "Y", "Z"
                    myNum = "9"
                Case Else
                    myNum = ""
            End Select
            PhoneKeyPadConverter = PhoneKeyPadConverter & myNum
        Next i
    End If
 
End Function

Then, for your example, the Excel formula would look like this:
=PhoneKeyPadConverter("JSMIT")

 

[marka87uk]

There may be a simpler way but I'd have a lookup table with each letter and it's corresponding number and then use the MID function to lookup the number for each letter.

 

[Gerald Higgins]

There are various ways to do this, but remember, the ID numbers may be non-unique.

For example,
JOHN SMITH
would generate the same ID as
KYLE ROGULSON
(I've never heard of the last name Rogulson, but you get the point )

 

[Joe4]

Good point Gerald!

This functionality might be fine for creating passwords (since they usually do not have to be unique), but probably not usernames (which usually do need to be unique), without something "extra" to address potential duplicates.

 

[T. Valko]

Good point Gerald!

This functionality might be fine for creating passwords (since they usually do not have to be unique), but probably not usernames (which usually do need to be unique), without something "extra" to address potential duplicates.

Another potential problem...

Last names that aren't at least 4 characters long.

Kevin Na
Anthony Kim
Michelle Wie
Ndamukong Suh

 

[yram]

Thanks, all, this is perfect!
We'd actually already thought of the issues mentioned, and intended to add another digit to address the duplicates. So, Jane Smith would be XXXXX1 and Joshua Smith would be XXXXX2 and Kyle Rogolsong would be XXXXX3, etc.

And Beth Lee would be XXXX0, Beth Li would be XXX00.

If anyone has a better idea for creating numeric id numbers (required by a "one size fits all" administration software program we're using) that won't be used for anything else, but need to be easily remembered (or recreated) by the users, I'm happy to hear any suggestions!!

The students are mostly all in the same zip code, and we won't have their phone numbers, and multiple kids in the same family would have the same phone numbers anyway. (The kids are mostly 4th grade and don't have cell phones yet.)

Thanks!!

 

[Joe4]

We deal with this sort of thing all the time. There are very few people identifiers that are guaranteed to be unique (something like Social Security Number is, but that is about it). If the IDs are being derived from any other personal information other than Social Security Number, you will need to address the potential for duplicates. As long as you have a plan for handling them, you should be fine.

Just note that they may not be entirely reproducable without knowing the ID (i.e. if you have situations with IDs that have a counter on the end because of a duplicate situation, there is no way of knowing what the value is for a person if you do not have it in front of you!).

As for the situation Biff raised, you need to determine if you want all values to be at least five long, or if it is OK for them to be shorter, If they need to be five, you can just zero pad the end (i.e. 258 -> 25800).