# Converting column number to excel lettered column help!

## Recommended Posts

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 on other sites

```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 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```

SciTE4AutoIt3 Full installer Download page   - Beta files

Live for the present,
Dream of the future,
Learn from the past.

##### 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 on other sites

Excel stops at column 256 = IV

Edited by Jos

SciTE4AutoIt3 Full installer Download page   - Beta files

Live for the present,
Dream of the future,
Learn from the past.

## 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