Convert Alphabet to number (a=1, b=2, etc.)

[xenou]

Is anyone aware of a funtion to return 1 for A, 2 for B, etc?

Thanks!

 

[Norie]

How about CODE?

=CODE(A1)-64

 

[xenou]

I've starting thinking I really need three functions: one to parse the department, one for the month, and one for the year...
My end result would be a Code to be parsed in cell A1, with Dept, Month, and Year presented in cells B1, C1, and D1 respectively.

 

[xenou]

If "A" is in A1,
then CODE(A1)-64 = 1.


If 1 is in B2,
then CHAR(64+B2) = "A"

Thanks. (and to Norie). This returns my month perfectly.

 

[mikerickson]

Here are a couple of functions. One can be called from a sheet, the other works better if called only from VB. They return string values for Dept, Month, Year, ABC, order .

Function decodeString(inputStr As String) As Variant
    Dim parseStr(1 To 5) As Variant
    
    parseStr(1) = Left(inputStr, 1) & " Dept"
    
    parseStr(2) = Format(DateSerial(1, Asc(Mid(inputStr, 2)) - 64, 1), "mmmm")
    parseStr(3) = CStr(1936 + Asc(Mid(inputStr, 3)))
    parseStr(4) = Mid(inputStr, 4, 1)
    parseStr(5) = Mid(inputStr, 5)
    
    decodeString = parseStr
End Function

Function decodeStringFromWorksheet(inputStr As String, index As Double) As Variant
 decodeStringFromWorksheet = decodeString(inputStr)(index)
End Function

Sub test()
Dim testStr As String
testStr = "BCGA014"
Dim i As Integer
For i = 1 To 5
    MsgBox decodeString(testStr)(i)
Next i
End Sub

 

[jindon]

Here's My goal:
I will be parsing a "code" that is as follows:

first digit is D or B and is a department
second digit is A thru L and is month (A=Jan, B=Feb, etc.)
third digit is G for 2007. or F for 2006, H for 2008, etc.
Fourth digit is A,B, or C for A, B, or C (easy one)
followed by a sequential numbering beginning with 001.

So: BCGA014 is B dept, March, 2007, A, 14th assigned code.

my goal is to build a custom parse function that doesn't require overmuch use of Select CASE, if possible. My standby would be to use Left, Right, and Mid type functions with good old Select Case statements.

1) Select B1:F1
2) =Alex(A1)
3) confirm with Ctrl + Shift + Enter

Function Alex(txt As String)
Dim myDept As String, myMonth As String
Dim myYear As Long, Easy1 As String, myNum As Long
myDept = Left$(txt,1) & " Dept"
myMonth = MonthName(Asc(UCase(Mid$(txt,2,1)))-64, True)
myYear = Asc(UCase(Mid$(txt,3,1))) -64 + 2000
Easy1 = Mid$(txt,4,1)
myNum = Mid$(txt,5)
Alex = Array(myDept, myMonth, myYear, Easy1, myNum)
End Function

 

[Norie]

Alexander

Any chance of a little more data?

One line isn't much to go on.