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!

 

[NBVC]

Try:

=COLUMN(INDIRECT(A1&1))

Where A1 houses the Letter to convert.

 

[xenou]

Sweet formula!
I should have noted this will be built into a custom function.

Is it possible something like this could be done with an array? Regards.

 

[NBVC]

Indirect() function doesn't like array formulas much....

What exactly are you trying to accomplish?

 

[xenou]

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.

 

[NBVC]

I'm not much of a VBA currently, so can't really help you in that department.

But...you can consider using some lookup tables containing some of these codes and associated labels.... and create a formula that "concatenates" these codes.

 

[xenou]

NP. I played around with indirect and column for a while ... very cool

I came up with:

Function ParseMonth(code As String) As Byte

ParseMonth = InStr("ABCDEFGHIJKL", Mid(code, 2, 1))

End Function

If anyone sees anything wrong with my function structure let me know--this is new territory for me. Regards.

 

[babycody]

Where I1 houses the letter you want to lookup.
=LOOKUP(I1,{"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z"},ROW(1:26))

 

[mikerickson]

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


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

 

[mikerickson]

It's not clear to me.

What value do you want myFunction(BCGA014) to be?

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.