Auto convert an alphanumeric string (CIS9638S) to numbers only?

SDesmond,
Here's a UDF (User-defined Function)

'/=============================================/
Public Function GetNumberFromString(strInput As String)
'Change letters to corresponding numbers and leave numbers
' as is. If neither letter nor number, return blank
'ex: C2D = 324 / Cis9638S = 3919963819
'
Dim iLen As Integer, iCount As Integer
Dim strItem As String, strAlpha As String
Dim strNumber As String, strOutput As String

On Error Resume Next

Application.Volatile

strAlpha = _
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
strNumber = "0123456789"

If Len(strInput) <> 0 Then
iLen = Len(strInput)
'review each item in the string one at a time
For iCount = 1 To iLen
strItem = Mid(strInput, iCount, 1)

Err.Clear

'check if the item is a number, if so, add to output
If IsError(Application.WorksheetFunction.Find(strItem, _
strNumber)) Then
Else
If Err.Number = 0 Then
strOutput = strOutput & strItem
End If
End If

Err.Clear

'check if the item is a letter, if so, add corresponding
' number to output - ie: a or A = 1 / s or S = 19
If IsError(Application.WorksheetFunction.Find(strItem, _
strAlpha)) Then
Else
If Err.Number = 0 Then
strOutput = strOutput & Asc(UCase(strItem)) - 64
End If
End If

Next iCount
End If

'return the number associated with the original string
GetNumberFromString = CDbl(strOutput)

End Function
'/=============================================/

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"SDesmond" wrote:

> I have a series of alphanumeric numbers (ex. CIS9638S) that I would like to
> automatically convert to a numeric string (ex. 3919963819) that need continue
> to be unique values. Is there a script or function that will convert alpha
> characters to a numeric representative?

Hi,

Your approach could lead to nonunique values upon conversion - e.g.,
AQM1111M, LBM1111M, and LUC111M will convert to the same number 12213111113.
However, you could transform the alphanumerics to unique numbers using ANSI
codes, with some limitations.
Are all the strings of the same format (i.e., 3 letters-4 numerals-1
letter), and are the letters always in capital case? If yes, the following
formula will generate unique numbers for the strings.
=CODE(LEFT(A1,1))&CODE(MID(A1,2,1))&CODE(MID(A1,3,1))&MID(A1,4,4)&CODE(RIGHT(A1,1))
If there are lower case letters and/or your strings do not have one common
format, a more elaborate(!) formula is needed.

Regards,
B.R. Ramachandran


"SDesmond" wrote:

> I have a series of alphanumeric numbers (ex. CIS9638S) that I would like to
> automatically convert to a numeric string (ex. 3919963819) that need continue
> to be unique values. Is there a script or function that will convert alpha
> characters to a numeric representative?

I have a series of alphanumeric numbers (ex. CIS9638S) that I would like to
automatically convert to a numeric string (ex. 3919963819) that need continue
to be unique values. Is there a script or function that will convert alpha
characters to a numeric representative?

SDesmond,
Here's a UDF (User-defined Function)

'/=============================================/
Public Function GetNumberFromString(strInput As String)
'Change letters to corresponding numbers and leave numbers
' as is. If neither letter nor number, return blank
'ex: C2D = 324 / Cis9638S = 3919963819
'
Dim iLen As Integer, iCount As Integer
Dim strItem As String, strAlpha As String
Dim strNumber As String, strOutput As String

On Error Resume Next

Application.Volatile

strAlpha = _
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
strNumber = "0123456789"

If Len(strInput) <> 0 Then
iLen = Len(strInput)
'review each item in the string one at a time
For iCount = 1 To iLen
strItem = Mid(strInput, iCount, 1)

Err.Clear

'check if the item is a number, if so, add to output
If IsError(Application.WorksheetFunction.Find(strItem, _
strNumber)) Then
Else
If Err.Number = 0 Then
strOutput = strOutput & strItem
End If
End If

Err.Clear

'check if the item is a letter, if so, add corresponding
' number to output - ie: a or A = 1 / s or S = 19
If IsError(Application.WorksheetFunction.Find(strItem, _
strAlpha)) Then
Else
If Err.Number = 0 Then
strOutput = strOutput & Asc(UCase(strItem)) - 64
End If
End If

Next iCount
End If

'return the number associated with the original string
GetNumberFromString = CDbl(strOutput)

End Function
'/=============================================/

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"SDesmond" wrote:

> I have a series of alphanumeric numbers (ex. CIS9638S) that I would like to
> automatically convert to a numeric string (ex. 3919963819) that need continue
> to be unique values. Is there a script or function that will convert alpha
> characters to a numeric representative?

Hi,

Your approach could lead to nonunique values upon conversion - e.g.,
AQM1111M, LBM1111M, and LUC111M will convert to the same number 12213111113.
However, you could transform the alphanumerics to unique numbers using ANSI
codes, with some limitations.
Are all the strings of the same format (i.e., 3 letters-4 numerals-1
letter), and are the letters always in capital case? If yes, the following
formula will generate unique numbers for the strings.
=CODE(LEFT(A1,1))&CODE(MID(A1,2,1))&CODE(MID(A1,3,1))&MID(A1,4,4)&CODE(RIGHT(A1,1))
If there are lower case letters and/or your strings do not have one common
format, a more elaborate(!) formula is needed.

Regards,
B.R. Ramachandran


"SDesmond" wrote:

> I have a series of alphanumeric numbers (ex. CIS9638S) that I would like to
> automatically convert to a numeric string (ex. 3919963819) that need continue
> to be unique values. Is there a script or function that will convert alpha
> characters to a numeric representative?