Function Names
excel coordinatesDescription
This function converts row nand column numbers into Microsoft Excel coordinates. Please note the following nuances:
- B4P table column 0 is A, columns 25 - 27 are Z, AA and AB respectively. And so forth.
- B4P table row 0 translates to Excel row 1
- If no parameters are applied, then the function looks for current row and column numbers in the context for partial table specifications. Exception will be asserted if no context info for partial table specifciaton is available.
- Exceptions will also be flagged if the row and column numbers are out of Excel range
Attention: No negative indexing is allowed here.
Call as: function
Restrictions
Indirect parameter passing is enabled
Parameter count
1-2
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
numeral | Column number The column number will translate to 1 or more letters. Valid range: 0..16383 |
| 2 input |
numeral | Row number The column number will be incremented by 1 and added after the column designation. Valid range: 0..1048575 Default value: No row number will be specified in the output |
Return value
| Type | Description |
|---|---|
| string | Excel coordinate If 1 parameter is provided, then the column designation will be provided. If 2 parameters are provided, then the Excel coordinates contain both column and row information. |
Examples
coord[A1] = { 0, 0 };
coord[B] = { 1 };
coord[Z10] = { 25, 9 };
coord[AA4] = { 26, 3 };
coord[AZ4] = { 51, 3 };
coord[BA14] = { 52, 13 };
coord[ZZ] = { 701 };
coord[AAA] = { 702 };
coord[XFD1048576] = { 16383, 1048575 };
for all variables( coord[], val[], i[], name[] )
{
coord[] = excel coordinates( val[] ); // Makes use of indirect parameter passing
echo("Input {col,row}: ", val[], " Result: ", coord[], " (Expected: ", name[], ")" );
}
echo( new line, "Apply the function with 0 parameters on a table:" );
table initialize ( blank table, {{ Name, Age }, { Zoe, 30 }, { Nic, 40 }} );
table process cells( blank table, echo( "Row: ", row(), " Column: ", col(), " Value: ", [.], " Excel coordinates: ", excel coordinates() ) );Output
Input {col,row}: {0,0} Result: A1 (Expected: A1)
Input {col,row}: {26,3} Result: AA4 (Expected: AA4)
Input {col,row}: {702} Result: AAA (Expected: AAA)
Input {col,row}: {51,3} Result: AZ4 (Expected: AZ4)
Input {col,row}: {1} Result: B (Expected: B)
Input {col,row}: {52,13} Result: BA14 (Expected: BA14)
Input {col,row}: {16383,1048575} Result: XFD1048576 (Expected: XFD1048576)
Input {col,row}: {25,9} Result: Z10 (Expected: Z10)
Input {col,row}: {701} Result: ZZ (Expected: ZZ)
Apply the function with 0 parameters on a table:
Row: 1 Column: 0 Value: Zoe Excel coordinates: A2
Row: 1 Column: 1 Value: 30 Excel coordinates: B2
Row: 2 Column: 0 Value: Nic Excel coordinates: A3
Row: 2 Column: 1 Value: 40 Excel coordinates: B3