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

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.

how to get the column number from the corresponding column name de
 

hi

here's a formula, not the best one, but it works:
=IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(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
" wrote:

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.

how to get the column number from the corresponding column nam
 

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


"Carlo" wrote:

hi

here's a formula, not the best one, but it works:
=IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(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
" wrote:

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.

how to get the column number from the corresponding column nam
 

Carlo wrote:

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


"Carlo" wrote:

hi

here's a formula, not the best one, but it works:
=IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(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
" wrote:

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.

hey i dont know how to implement the formula you have given.
just answer me like this

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

how to get the column number from the corresponding column nam
 

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

" wrote:


Carlo wrote:

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


"Carlo" wrote:

hi

here's a formula, not the best one, but it works:
=IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(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
" wrote:

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.

hey i dont know how to implement the formula you have given.
just answer me like this

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

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

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

On 10 Sep 2006 22:39:22 -0700, wrote:

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.