Convert Column Letters to Number

[Polos]

Hi!

I'm looking for a function to convert a "large" column letters (up t
10 characters) to it's equivalent in number.

e.g.

Input=A
Output=1

Input=AAA
Output=703

Input=EXCEL
Output=2,708,874

Any suggestions?

Regards

 

[Myrna Larson]

Here's a VBA function that gives the results you want:

Option Explicit

Function ColumnToNumber(sText As String) As Variant
Dim Bytes() As Byte
Dim Letter As Long
Dim Multiplier As Double
Dim N As Double
Dim Total As Double

ColumnToNumber = CVErr(xlErrValue)
If Len(sText) = 0 Then Exit Function

Bytes() = UCase$(sText)
Total = 0
Multiplier = 1

'least significant letter is at the top of the array
For Letter = UBound(Bytes()) - 1 To 0 Step -2

N = Bytes(Letter) - 64 'A = 65: translate to 1
If N < 1 Or N > 26 Then Exit Function

Total = Total + N * Multiplier
Multiplier = Multiplier * 26

Next Letter

ColumnToNumber = Total

End Function