Sign in to follow this  
Followers 0
laffo16

Converting column number to excel lettered column help!

5 posts in this topic

Hello everyone, need to make a function which converts a number into the excel formating of the column number. eg.

1 = A

2 = B

27 = AA

28 = AB

sending the numbers and returning the characters. shouldnt be differcult, i'll post the code once im finished, but if anyone has any suggestions for the easiest & fastest way to do this, im all ears.

cheers!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

MsgBox(0, "", _Convert_Num_to_ExcelCol(1))
MsgBox(0, "", _Convert_Num_to_ExcelCol(26))
MsgBox(0, "", _Convert_Num_to_ExcelCol(27))
MsgBox(0, "", _Convert_Num_to_ExcelCol(52))
MsgBox(0, "", _Convert_Num_to_ExcelCol(53))

Func _Convert_Num_to_ExcelCol($CNA_num)
    $CNE_array = StringSplit("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", ",")
    $CNE_int = 0
    While 1
        $CNA_num += -26
        $CNE_int += 1
        If $CNA_num < 1 Then
            If $CNE_int = 1 Then
                $CNA_num += 26
                Return $CNE_array[$CNA_num]
            ElseIf $CNE_int >= 2 Then
                $CNA_num += 26
                Return $CNE_array[$CNE_int -1] & $CNE_array[$CNA_num]
            EndIf
        EndIf
    WEnd
EndFunc

well thats as short as i think i can get it.

Edited by laffo16

Share this post


Link to post
Share on other sites

Think this should be close:

Func _Convert_Num_to_ExcelCol($CNA_num)
    $CNE_array = StringSplit("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", ",")
    $CNE_array[0]=""
    $char1 = int(($CNA_num-1)/26)
    $char2 = $CNA_num - $char1*26
    Return $CNE_array[$char1] & $CNE_array[$char2]
EndFunc  ;==>_Convert_Num_to_ExcelCol

Visit the SciTE4AutoIt3 Download page for the latest versions  - Beta files                                How to post scriptsource        Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

thats the one Jos, thanks m8.

the reason i needed this, is because i delete (or clear) cells in bulk using a copy of a blank cell and paste into a range (A1:B2). i needed number control over the rows:columns which is pasted into.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0