Figure out Excel Cell Name

HCL Domino Domino Forum
1

Hello all… I’m trying to come up with a way to determine what cell in an excel spreadsheet to populate next but just can’t seem to figure it out.

So, excel columns are named a,b,c,d,e,etc… all the way to z, then they start at aa thru az then ba thru bz and so on. I’m trying to come up with a way to pass a function a number 1 through whatever and have the function return the corresponding column. For example, 1 should return A, or, 27 should return AA, or, 53 should return BA, etc. How can I go about this? I’ve been messing with this for a while now and just can’t seem to figure it out.

Thanks,

Keith

2

Subject: Figure out Excel Cell Name

Off the top of my head, the may would seem to be to use the modulo functionality:

@If(num > 26; @Char(64+(num/26)); “”)+@Char(64+@Modulo(num; 26))

would work in formula language, for example.

3

Subject: RE: Figure out Excel Cell Name

Thanks Ben! However, I’m trying to use this in LotusScript and can’t seem to get it to work. Here’s the conversion I came up with but keep getting “Unexpected: mod: Expected Expression”

convNumToAlph = Chr(64+(num/26)) & Chr(64+Mod(num,26))

Thanks,

Keith

4

Subject: RE: Figure out Excel Cell Name

OK, I got it now, was using Mod wrong… using

convNumtoAlph = Cstr(Chr(64+(num/26)) + Chr(64+(num Mod 26)))

Keith

5

Subject: Figure out Excel Cell Name

I’ve always used this.

Public Function getColumnName( CurrentColumn As Integer) As String

' Columns in Excel Start with A thru Z, then they go AA thru AZ, then BA thru BZ.. However, the stop

' after 256 columns are used.



getColumnName = ""		



If CurrentColumn\27> 0 Then

	getColumnName = Chr$( 64 + (CurrentColumn + 1)\27)

End If

If CurrentColumn Mod 26 = 0 Then

	getColumnName = getColumnName + "Z"

Else			

	getColumnName = getColumnName + Chr$( 64 + ( CurrentColumn Mod 26))

End If

End Function

6

Subject: RE: Figure out Excel Cell Name

Thanks everyone for all your help. Here’s what I ended up with:

Private Function convNumtoAlph(num As Integer) As String

If num <= 26 Then

	convNumToAlph = Cstr(Chr(64+(num Mod 26)))

Elseif num > 26 Then

	convNumToAlph = Cstr(Chr(64+(num/26)) + Chr(64+(num Mod 26)))

End If

End Function

7

Subject: RE: Figure out Excel Cell Name

there is a bug with the Z columns, here the fixed version

If num <= 25 Then

	convNumToAlph =Chr$(64+(num Mod 26))

Elseif num = 26 Then		

	convNumtoAlph = "Z"

Else

	convNumtoAlph = Chr$( 64 + ((num-1) \ 26))

	If num Mod 26 = 0 Then

		convNumtoAlph = convNumtoAlph + "Z"

	Else

		convNumtoAlph = convNumtoAlph + Chr$( 64 + ( num Mod 26))

	End If			

End If