Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gary L Brown
 
Posts: n/a
Default 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 = _
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz"
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

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?

  #2   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
random alphanumeric string [email protected] Excel Worksheet Functions 4 April 21st 23 09:04 AM
Function to convert Time String to Time Andibevan Excel Worksheet Functions 6 August 19th 05 01:19 PM
How to COnvert text string to number timtak Excel Discussion (Misc queries) 3 June 4th 05 04:57 AM
Auto Shorten a Data String Alan Excel Discussion (Misc queries) 1 May 11th 05 07:05 AM
How to convert string to a date galsaba Excel Worksheet Functions 3 March 4th 05 06:20 PM


All times are GMT +1. The time now is 10:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"