[Solved] Calc Basic - Converting Numbers to Column Letters

[BrettN]

Hi Everyone,
Couldn't find this on the forum so forgive me if this is a duplicated post but here's my code I use to convert numbers to column letters.
Example:
ColumnNumberToLetters(0) = "A"
ColumnNumberToLetters(25) = "Z"
ColumnNumberToLetters(1023) = "AMJ" (Max columns in a spreadsheet)

Let me know if you find any bugs or have any questions.
Thanks,
BN

Code: Select all

Function ColumnNumberToLetters(ColumnNumber as integer) as string  
	Dim FirstLetter as string, SecondLetter as string
	if ColumnNumber >= 0 then
		if ColumnNumber < 26 then
			ColumnNumberToLetters = Chr(65 + ColumnNumber)
		elseif ColumnNumber <= 1023 then
			If ColumnNumber >= 702 Then
				ColumnNumber = ColumnNumber - 702
				ColumnNumberToLetters = "A"
			end if
			FirstLetter = Chr(Int((ColumnNumber) /26)+65)
			SecondLetter = Chr(((ColumnNumber) mod 26) + 65)
			ColumnNumberToLetters = ColumnNumberToLetters & FirstLetter & SecondLetter
		else
			ColumnNumberToLetters="There's no column letter past 'AMJ' aka column number larger than '1023' "
		end if
	else
		ColumnNumberToLetters="Please use a number greater than or equal to [0] "
	end if
End Function

Better Code @Lupp

Code: Select all

Function ColumnNumberToLetters(ColumnNumber as integer) as string
	Dim fa
	fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
	ColumnNumberToLetters= fa.callFunction("ADDRESS", Array(1, ColumnNumber ,4))
	ColumnNumberToLetters= Mid(ColumnNumberToLetters,1,Len(ColumnNumberToLetters)-1)
End Function

[MrProgrammer]

Hi, and welcome to the forum.

here's my code I use to convert numbers to column letters.
{20-line Basic macro}

=SUBSTITUTE(ADDRESS(1; ColumnNumber ;4);1;"")

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

For use inside a sheet the suggestion by MrProgrammer is surely recommendable. For usage in some specific code a specialized function may be preferable. There may also be cases where column names for TextTable objects in Writer must be computed.

In these different cases a solution should be based on a mathematical concept: The alphabetic column names are simply numbers coded in a place-weighted system without a digit for 0 (zero). For spreadsheets the letters A through Z (upper case) are used with the values from 1 through 26. For text tables the radix is 52 and the characters a through z are used with the values 27 through 52 (funny!). The following code (13 lines, comments not counted) is suitable in both cases:

Code: Select all

Function columnNameForColumnIndex(pColNum As Long, pMode As Boolean)             REM 1-based!
REM The column names will consist of plain Latin letters. Their values as digits 
REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
REM There is NO ZERO in this system.
REM pMode=True (or<>0) tells that the column names are made for TextTable. 
REM Otherwise for Spreadsheet.
digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
radix = 26
If pMode Then radix = 52
nameStr = ""
Do While pColNum>0
  dValue = (pColNum MOD radix)
  If dValue=0 Then dValue = radix
  pColNum    = Int((pColNum - dValue)/radix)
  nameStr = Mid(digits, dValue, 1) & nameStr
Loop
columnNameForColumnIndex = nameStr
End Function

[BrettN]

Hi @lupp,
I use the code to create formula's. Here's an example of that use case. (the hard set variables are just for demonstration purposes)

Code: Select all

Qty = 4
LineNum = 1
CurCol = 26
Curformula = "=" & Qty & "-" & ColumnNumberToLetters(CurCol-1) & LineNum+1

oSheet = ThisComponent.getCurrentController.getActiveSheet
oSheet.getCellByPosition(CurCol, LineNum).formulalocal = Curformula

reuslt:
Cell("AA2").formulalocal = "=4-Z2"

Let me know if you have any questions.
Thanks,
BN

[Lupp]

Just a remark. You can use my code in the context e.g. if you want.

You can also use the appropriate standard function of Calc calling it via a FunctionAccess object:

Code: Select all

fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
address = fa.callFunction("ADDRESS", Array(LineNumber, ColumnNumber ,4)) 

You only need to make sure to have LineNumber and ColumnNumber as Calc uses them (not like the 0-based indices used by the API).
This surely needs less code in the given case. (The functions I proposed are made for a more general scope.)

What I actually don't understand is for what reason you use the .FormulaLocal property (instead of simply .Formula). It may (not in this simplified case, but in more substantial ones) hinder the usability of the code in a different locale.
Whether or not setting formulas by user code is appropriate, I cannot judge without knowing the actual use-case.

[BrettN]

Hi @Lupp,
I never knew about that function

Code: Select all

callFunction

Thank you
That saves me a bunch of lines and helps fix a few other issues with another code

I've been using formulalocal because I only knew about formulalocal from running the macro recorder.
From a quick google search i couldn't find the difference but I'm going to replace all formulalocal with formula and see if anything breaks

[Villeroy]

Code: Select all

Function getColumnName(doc, c As Long)
	getColumnName = doc.Sheets.getByIndex(0).Columns.ElementNames(c)
End Function

Pass a spreadsheet document and a column index

[donknudson]

Here's an "easy" (ha ha) formula to convert a cell reference to text

=CONCATENATE(CHAR(COLUMN(<cell reference>)+64),ROW(<cell reference>))

For example, to convert the cell reference C7 to the text string "C7":
=CONCATENATE(CHAR(COLUMN(C7)+64),ROW(C7))

To have a cell display its own address in text format:
=CONCATENATE(CHAR(COLUMN()+64),ROW())

Note that, ironically, the equals sign (=) at the beginning of the formula alerts the spreadsheet program that you are entering a formula and not "just text," yet the value that the formula returns is of type "text" ; - )

[donknudson]

Thanks, MrProgrammer. The address() function is simpler yet, and not limited to single-character column names -- also provides "R1C1" style as well as "A1" style cell references.

Here are some examples of formulas entered into cell C7:

Formula --- Result
=ADDRESS(ROW(),COLUMN(),1,0) --- R7C3
=ADDRESS(ROW(),COLUMN(),1) --- $C$7
=ADDRESS(ROW(),COLUMN(),2) --- C$7
=ADDRESS(ROW(),COLUMN(),3) --- $C7
=ADDRESS(ROW(),COLUMN(),4) --- C7
=ADDRESS(ROW(),COLUMN()) --- $C$7
=ADDRESS(70,77,4) --- BY70

: - )