Generate Alphabetical Codes Based on Numbers

[Haree]

Hi all I have to generate codes based on numbers based on the following standard

A,B,C,D,E,F,G,H,I,J
1,2,3,4,5,6,7,8,9,0

If i enter 11.03 it should show AA.JB
if i enter 67.85 it should show FG.HE

Thanks in advance.

 

[Joe4]

One way is to create our own function in VBA (a "User Defined Function" or "UDF").

So that code may look something like this:

Function GenCode(entry As Variant) As String
'   Convert a numeric entry to a code

    Dim i As Long
    Dim c As String
    Dim x As String
    Dim str As String
    
    If Len(entry) > 0 Then
        For i = 1 To Len(entry)
            c = Mid(entry, i, 1)
            Select Case c
                Case "."
                    x = "."
                Case "0"
                   x = "J"
                Case "1", "2", "3", "4", "5", "6", "7", "8", "9"
                    x = Chr(64 + Val(c))
            End Select
            str = str & x
        Next i
    End If
    
    GenCode = str
    
End Function

Then we can use it in VBA or on the worksheet like any other Excel function, i.e

=GenCode(A1)

for value in cell A1

 

[Haree]

Thank you so much Joe , worked like a charm

 

[Rick Rothstein]

For those who might be interested, here is a more compact way to write the GenCode function...

Function GenCode(ByVal entry As Variant) As String
  Dim x As Long
  For x = 1 To Len(entry)
    If Mid(entry, x, 1) Like "[0-9]" Then Mid(entry, x) = Chr(64 + Mid(entry, x, 1))
  Next
  GenCode = Replace(entry, "@", "J")
End Function

 

[Joe4]

You are welcome.
Glad we were able to help.