laffo16 Posted October 12, 2008 Share Posted October 12, 2008 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! Link to comment Share on other sites More sharing options...
laffo16 Posted October 12, 2008 Author Share Posted October 12, 2008 (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 October 12, 2008 by laffo16 Link to comment Share on other sites More sharing options...
Developers Jos Posted October 12, 2008 Developers Share Posted October 12, 2008 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 SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
laffo16 Posted October 12, 2008 Author Share Posted October 12, 2008 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. Link to comment Share on other sites More sharing options...
Developers Jos Posted October 13, 2008 Developers Share Posted October 13, 2008 (edited) Excel stops at column 256 = IV Edited October 13, 2008 by Jos SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now