how to get the column number from the corresponding column name denoting initial..???

[theslaveofgod]

hi,
i want to recieve the column number ( ranging from 1 to 256 ) while
the input will be the column name (ranging from A to IV ). please
provide me a macro solution to reach the above mentioned requirement.

Thanks and Regards
Abu.

 

[Guest]

hi

here's a formula, not the best one, but it works:
=IF(A1>26;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHAR(A1+64))

A1 is the input field, where the user can enter the column-number!
hth

If anyone has a better solution, please post it, i'm really interested in it.

Cheers
Carlo

 

[Guest]

hi

here's a formula, not the best one, but it works:
=IF(A1>26;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHAR(A1+64))

A1 is the input field, where the user can enter the column-number!
hth

If anyone has a better solution, please post it, i'm really interested in it.

Cheers
Carlo

 

[Guest]

Just saw, that you want the other way around
it's even easier that way

=IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input")

hth

Carlo

 

[Guest]

Just saw, that you want the other way around
it's even easier that way

=IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input")

hth

Carlo

 

[theslaveofgod]

Just saw, that you want the other way around
it's even easier that way

=IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input")

hth

Carlo

just answer me like this

function col_number(Col_name)
col_name = "AB"
col_no = ????????
msgbox col_no
end function

 

[theslaveofgod]

Just saw, that you want the other way around
it's even easier that way

=IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input")

hth

Carlo

just answer me like this

function col_number(Col_name)
col_name = "AB"
col_no = ????????
msgbox col_no
end function

 

[Guest]

Hi Abu,

as you are in the Worksheet function forum, i assumed you
need a formula for a cell, heres the code:
'----------------------------------
Function col_number(col_name)

If Len(col_name) = 1 Then
col_no = Asc(col_name) - 64
ElseIf Len(col_name) = 2 Then
col_no = (Asc(Left(col_name, 1)) - 64) * 26 + Asc(Right(col_name, 1)) - 64
Else
col_no = "Wrong Input"
End If

MsgBox col_no

End Function
'----------------------------------

Please choose the right forum

hth

Cheers Carlo

 

[Guest]

Hi Abu,

as you are in the Worksheet function forum, i assumed you
need a formula for a cell, heres the code:
'----------------------------------
Function col_number(col_name)

If Len(col_name) = 1 Then
col_no = Asc(col_name) - 64
ElseIf Len(col_name) = 2 Then
col_no = (Asc(Left(col_name, 1)) - 64) * 26 + Asc(Right(col_name, 1)) - 64
Else
col_no = "Wrong Input"
End If

MsgBox col_no

End Function
'----------------------------------

Please choose the right forum

hth

Cheers Carlo

 

[Gord Dibben]

Abu

Couple of UDF's.

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

usage.........=getcolnum("a") returns 1

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

usage........=getcollet(27) returns AA


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Abu

Couple of UDF's.

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

usage.........=getcolnum("a") returns 1

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

usage........=getcollet(27) returns AA


Gord Dibben MS Excel MVP