Convert cell names in spreadsheets to numbers Topic is solved

[Albireo]

In a spreadsheet all cells have a name eg. B3, C8, F12 and so on.
All cells have also a number. eg.

• A1 => 0,0
• B3 => 1,2
• C8 => 2,7
• F12 => 5,11

The first step is to be able to translate A =>0, B =>1, C =>2, etc. on an easy way.
(Can be done in this way)

Code: Select all

Col = ABCDEFGHIJKLMNOPQRSTUVXYZ

Cell = C3
CellCol := InStr(Col, SubStr(Cell, 1, 1)) - 1
CellRow := SubStr(Cell, 2) - 1

MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,% "Converted result .: " CellCol " , " CellRow

But the next columns?...
After Column Z is AA, AB, AC...AZ, and than BA, BB, BC....

It is no solution to write all columns in a long string...
Is there a better way to handle this desire?

[boiler]

Make sure to use capital letters:

Code: Select all

loop
{
	InputBox, CellName, Cell Name, Input cell name:`n(Cancel to exit),, 180, 150
	if ErrorLevel
		ExitApp
	RegExMatch(CellName, "([A-Z]+)(\d+)", Part)
	if (StrLen(Part1) = 1)
		ColNum := Asc(Part1) - 65
	else
		ColNum := (Asc(SubStr(Part1, 1, 1)) - 64) * 26 + Asc(SubStr(Part1, 2, 1)) - 65
	MsgBox, % ColNum "," Part2 - 1
}

[flyingDman]

Or have Excel calculate it:

Code: Select all

cll := "f12"
Xl := ComObjCreate("Excel.Application") 
Xl.Workbooks.Add 						
msgbox % XL.activesheet.range(cll).column - 1 "," XL.activesheet.range(cll).row - 1
xl.Quit()
xl := ""

[Albireo]

Thanks everyone!
Is it complcated the other way?
From 26,127 to AA128?

[jmeneses]

Code: Select all

pX := 26
pY := 127
Msgbox 0x40000,, % ExcelColumnNumberToName(px+1) pY +1
ExitApp

; https://www.autohotkey.com/boards/viewtopic.php?f=5&t=34244&p=164723#p164723
ExcelColumnNumberToName(x) {
 Return (x<1 ? "" : ExcelColumnNumberToName(Floor((x - 1) / 26)) Chr(Mod(x - 1, 26) + Asc("A")) )
}

[Albireo]

Thank you! (but i'm using LibreOffice Calc )

This is my function ConvertCellnameToNumbers to convert from LibreOffice Calc name to LibreOffice Calc numbers .:

Code: Select all

InputBox, CellNameLO, Cell Name, Input cell name:`n(Cancel to exit),, 180, 150
if ErrorLevel
	ExitApp

CellNum := ConvertCellnameToNumbers(CellNameLO)
MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,% CellNameLO "`nConverted result .: " CellNum

ExitApp


ConvertCellnameToNumbers(CellName)
{	; Version .: 19 sept 2019
	; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=68131&p=293014#p293014 (by boiler)
	StringUpper CellName, CellName
	RegExMatch(CellName, "([A-Z]+)(\d+)", Part)
	if (StrLen(Part1) = 1)
		ColNum := Asc(Part1) - 65
	else
		ColNum := (Asc(SubStr(Part1, 1, 1)) - 64) * 26 + Asc(SubStr(Part1, 2, 1)) - 65
	
	Return ColNum "," Part2 - 1
}

[Odlanir]

Range "AA128" is Row 128 column 27 as you can see by this:

Code: Select all

xl := ComObjActive("Excel.Application")
MsgBox % xl.Range("AA128").Address(,,xlR1C1:=-4150)

If you change the references style to R1C1 in the EXCEL options you will see numbers instead of letters in the column header.
https://excelchamps.com/blog/r1c1-reference-style/

[jmeneses]

Range "AA128" is Row 128 column 27 as you can see by this:

Code: Select all

xl := ComObjActive("Excel.Application")
MsgBox % xl.Range("AA128").Address(,,xlR1C1:=-4150)

If you change the references style to R1C1 in the EXCEL options you will see numbers instead of letters in the column header.
https://excelchamps.com/blog/r1c1-reference-style/

The best

[boiler]

Range "AA128" is Row 128 column 27 as you can see by this:

Code: Select all

xl := ComObjActive("Excel.Application")
MsgBox % xl.Range("AA128").Address(,,xlR1C1:=-4150)

If you change the references style to R1C1 in the EXCEL options you will see numbers instead of letters in the column header.
https://excelchamps.com/blog/r1c1-reference-style/

That is with Excel. OP said he is using LibreOffice Calc, which apparently uses (0,0) instead of R1C1 when numerically referencing the cells.

[Odlanir]

@boiler The math is simple with R1C1 data:

Code: Select all

xl := ComObjActive("Excel.Application"), addr := "AA128"
ExcelAddr := xl.Range(addr).Address(,,xlR1C1:=-4150)
RegExMatch(ExcelAddr,"O)R(\d+)C(\d+)",m)
MsgBox % "Address`t" addr "`nRow`t"m.1-1 "`nColumn`t" m.2-1

[Albireo]

My problem is for LibreOffice Calc (and maybe OpenOffice Calc)...

I used the solution from @boiler above, and supplemented it so that both a cell D3, and a range of cells D3:F5, can be specified.
The result is then placed in an object, like this .:

Function ConvCellToNum - Calc

Code: Select all

ConvCellToNum(CellName)
{	StringUpper CellName, CellName
	oCellNum := {}	; Creates an object.
	
	If !InStr(CellName, ":")	; Only one cell!
	{	RegExMatch(CellName, "([A-Z]+)(\d+)", Part)
		if (StrLen(Part1) = 1)
			ColNum := Asc(Part1) - 65
		else
			ColNum := (Asc(SubStr(Part1, 1, 1)) - 64) * 26 + Asc(SubStr(Part1, 2, 1)) - 65
		oCellNum[1] := ColNum
		oCellNum[2] := Part2 - 1
	}
	else	; eg. C5:D5 - a cellrange
	{	FoundPos := InStr(CellName, ":")
		Name1 := SubStr(CellName, 1, FoundPos - 1)
		Name2 := SubStr(CellName, FoundPos + 1)

		Loop 2
		{	NumVar += 1	; oCellNum[1] & [2]
			RegExMatch(Name%A_Index%, "([A-Z]+)(\d+)", Part)
			if (StrLen(Part1) = 1)
				ColNum := Asc(Part1) - 65
			else
				ColNum := (Asc(SubStr(Part1, 1, 1)) - 64) * 26 + Asc(SubStr(Part1, 2, 1)) - 65
			
			oCellNum[NumVar] := ColNum
			NumVar += 1

			oCellNum[NumVar] := Part2 - 1
		}

		oCellNum[5] := ":"	; Indicates a cell range - maybe for later use
	}
	Return % oCellNum
}

But I got a problem I was not expecting.
This works .:

Code: Select all

C1 = 6
C2 = 9
C3 = 8
C4 = 10
oCells := oSheet.getCellRangeByPosition(C1,C2,C3,C4)	; G10:I11 - 6,9,8,10

But this does not work

Code: Select all

D1 := oCellNum[1]
D2 := oCellNum[2]
D3 := oCellNum[3]
D4 := oCellNum[4]
oCells := oSheet.getCellRangeByPosition(D1,D2,D3,D4)	; G10:I11 - 6,9,8,10

And this doesn't work either ...

Code: Select all

oCells := oSheet.getCellRangeByPosition(% oCellNum[1], % oCellNum[2], % oCellNum[3], % oCellNum[4])

what to do?

[boiler]

@boiler The math is simple with R1C1 data:

Code: Select all

xl := ComObjActive("Excel.Application"), addr := "AA128"
ExcelAddr := xl.Range(addr).Address(,,xlR1C1:=-4150)
RegExMatch(ExcelAddr,"O)R(\d+)C(\d+)",m)
MsgBox % "Address`t" addr "`nRow`t"m.1-1 "`nColumn`t" m.2-1

I don't understand what you're saying. He doesn't have Excel. Apparently, the upper-left cell in his spreadsheet is not 1,1 (or R1C1), it's 0,0. And he's not converting from R1C1 since that's Excel specific.

[boiler]

My problem is for LibreOffice Calc (and maybe OpenOffice Calc)...

Sorry, since I don't have LibreOffice Calc, I can't really test out what's going on with things like the following:

oCells := oSheet.getCellRangeByPosition(C1,C2,C3,C4) ; G10:I11 - 6,9,8,10[/code]

[Albireo]

Thanks for your time!

Code: Select all

oCells := oSheet.getCellRangeByPosition(C1,C2,C3,C4)	; G10:I11 - 6,9,8,10

Understands that not everyone can test, but I see no difference on C1 = 6 and D1 := oCellNum[1]
I think it's something else.
This work oCells := oSheet.getCellRangeByPosition(C1, C2, C3, C4)
but this does not oCells := oSheet.getCellRangeByPosition(D1, D2, D3, D4)

[sinkfaze]

Since modern spreadsheets breech three alpha chars for the columns, a slight modification to boiler's code:

Code: Select all

Loop
{
	InputBox, CellName, Cell Name, Input cell name:`n(Cancel to exit),, 180, 150
	if	ErrorLevel
		ExitApp
	RegExMatch(CellName, "([A-Z]+)(\d+)", Part), RowNum :=	Part2 - 1
	if	(StrLen(Part1) = 3)
		ColNum :=	((Asc(SubStr(Part1,1,1))-64)*676) + ((Asc(SubStr(Part1,2,1))-64)*26) + (Asc(SubStr(Part1,0))-65)
	 else if	(StrLen(Part1) = 2)
		ColNum :=	((Asc(SubStr(Part1,1,1))-64)*26) + (Asc(SubStr(Part1,0))-65)
	 else	ColNum :=	Asc(Part1)-65
	MsgBox, % ColNum "," RowNum
}

[Albireo]

Since modern spreadsheets breech three alpha chars for the columns, a slight modification to boiler's code:

Code: Select all

Loop
{
	InputBox, CellName, Cell Name, Input cell name:`n(Cancel to exit),, 180, 150
	if	ErrorLevel
		ExitApp
	RegExMatch(CellName, "([A-Z]+)(\d+)", Part), RowNum :=	Part2 - 1
	if	(StrLen(Part1) = 3)
		ColNum :=	((Asc(SubStr(Part1,1,1))-64)*676) + ((Asc(SubStr(Part1,2,1))-64)*26) + (Asc(SubStr(Part1,0))-65)
	 else if	(StrLen(Part1) = 2)
		ColNum :=	((Asc(SubStr(Part1,1,1))-64)*26) + (Asc(SubStr(Part1,0))-65)
	 else	ColNum :=	Asc(Part1)-65
	MsgBox, % ColNum "," RowNum
}

Thanks!

I found the problem (but not the solution...)
This work .:D1 := oCellNum[1]
D2 := oCellNum[2]
D3 := oCellNum[3]
D4 := oCellNum[4]

D1 = %D1%
D2 = %D2%
D3 = %D3%
D4 = %D4%

oCells := oSheet.getCellRangeByPosition(D1,D2,D3,D4) ; G10:I11 - 6,9,8,10
The object is text (not numbers) in D1 (before I made it as number D1 = %D1%
Is there any other, better way to handle this on?

[Albireo]

Thanks @sinkfaze !

I found the problem (but not the solution...)
This work .:

Code: Select all

D1 := oCellNum[1]
D2 := oCellNum[2]
D3 := oCellNum[3]
D4 := oCellNum[4]

D1 = %D1%
D2 = %D2%
D3 = %D3%
D4 = %D4%

oCells := oSheet.getCellRangeByPosition(D1,D2,D3,D4)	; G10:I11 - 6,9,8,10

The object is text (not numbers) in D1 (before I made it as number D1 = %D1%
Is there any other, better way to handle this on?

I have tried to convert the result in the function() like this.:
...
oCellNum[NumVar] = %ColNum%
NumVar += 1
oCellNum[NumVar] = %RowNum%
...
But when the object is copied to the mainprogram Return % oCellNum the object seams to be text again...

[MysticDude]

These have been translated from the links provided

Code: Select all

; https://stackoverflow.com/questions/667802/what-is-the-algorithm-to-convert-an-excel-column-letter-into-its-number
ColumnLetterToNumber(l) {
    n := 0
    loop % StrLen(l) {
        n *= 26
        n += Asc(SubStr(l, A_Index, 1)) - 64
    }
    return n
}

; https://learn.microsoft.com/en-us/office/troubleshoot/excel/convert-excel-column-numbers
ColumnNumberToLetter(n) {
    l := ""
    while n > 0 {
        a := Floor((n - 1) / 26)
        b := Mod((n - 1), 26)
        l := Chr(b + 65) . l
        n := a
    }
    return l
}

ColumnLetterToNumber for AHK V2

Code: Select all

ColumnLetterToNumber(l) {
    n := 0
    loop StrLen(l) {
        n *= 26
        n += Ord(SubStr(l, A_Index, 1)) - 64
    }
    return n
}