Where strCharacter holds your character. Ascii value for A is 65 so subtracting 64 gives you 1 and so on. UCase ensures that you get the correct value regardless of the case of the letter.
Ahh didn't read the whole question, hold on a moment:
Function GetNumberFromString(ByVal NumberString As String)
Dim intNumber As Integer
If Len(NumberString) > 1 Then
'Get the value for the first letter
intNumber = (Asc(Left(UCase(NumberString),1)) - 64) * 26 'Multiply by 26 to get the correct value
End If
intNumber = intNumber + Asc(Right(UCase(NumberString),1)) - 64 'Getting right hand char is fine even if a single char string
GetNumberFromString = intNumber
End Function
Just call the function:
Msgbox GetNumberFromString("AA") for example
0
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Function GetStringFromNumber(ByVal Number As Integer) As String
Dim strNumber As String
If Number > 26 Then
'Get the value for the first letter
If Number Mod 26 = 0 Then
Number = Number - 26
End If
strNumber = Chr((Int(Number / 26)) + 64)
Number = ((Number - 1) Mod 26) + 1
End If
strNumber = strNumber & Chr(Number + 64)
GetStringFromNumber = strNumber
End Function
Should do it though perhaps not the most efficient way.
Haydn I am happy to buy/make you a cup of coffee or even a beer if you are ever in Southampton area. You know what it is like when you start down one route and cannot see the wood for the trees. Same solution just much neater!
You need to consider how you treat the "zero" , e.g. in Decimal the next number after 9 is 10, in this "base 26" the next number after Z is AA not A0.
If you treat AA as 'Zero' then the following would work however the transformation does not commence in the single alpha characters only in the double characters.
Use Asc(UCase(strCharacter)) - 64
Where strCharacter holds your character. Ascii value for A is 65 so subtracting 64 gives you 1 and so on. UCase ensures that you get the correct value regardless of the case of the letter.
Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com