Page 1 of 1
Convert cell names in spreadsheets to numbers
Posted: 18 Sep 2019, 17:15
by 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?
Re: Convert cell names in spreadsheets to numbers Topic is solved
Posted: 18 Sep 2019, 18:01
by 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
}
Re: Convert cell names in spreadsheets to numbers
Posted: 18 Sep 2019, 19:48
by 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 := ""
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 04:19
by Albireo
Thanks everyone!
Is it complcated the other way?
From 26,127 to AA128 ?
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 04:34
by 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")) )
}
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 05:16
by 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
}
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 05:37
by 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/
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 07:24
by jmeneses
Odlanir wrote: ↑ 19 Sep 2019, 05:37
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
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 07:41
by boiler
Odlanir wrote: ↑ 19 Sep 2019, 05:37
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.
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 08:17
by 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
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 08:51
by 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?
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 09:05
by boiler
Odlanir wrote: ↑ 19 Sep 2019, 08:17
@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.
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 09:12
by boiler
Albireo wrote: ↑ 19 Sep 2019, 08:51
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]
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 09:48
by Albireo
Thanks for your time!
boiler wrote: ↑ 19 Sep 2019, 09:12
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)
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 09:49
by 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
}
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 10:03
by Albireo
sinkfaze wrote: ↑ 19 Sep 2019, 09:49
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?
Re: Convert cell names in spreadsheets to numbers
Posted: 19 Sep 2019, 10:33
by 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...
Re: Convert cell names in spreadsheets to numbers
Posted: 05 Dec 2023, 09:12
by 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
}