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.
Vectorization: This function supports vectorization in the 1st and 2nd function parameter.
Instead of providing a single value, you can provide a set or even a nested set which contain multiple values.
The function will then process every value and its return value contains a corresponding set containing all results.
Call as: function
Restrictions
Indirect parameter passing is disabled
Vectorization is allowed in the 1st and 2nd function parameters
Parameter count
1-2
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
numeral set |
Column number The column number will translate to 1 or more letters. Valid range: 0..16383. For Vectorization, provide a (nested) set containing mulitple values. |
| 2 input |
numeral set |
Row number The column number will be incremented by 1 and added after the column designation. Valid range: 0..1048575, For Vectorization, provide a (nested) set containing mulitple values. Default value: No row number will be specified in the output |
Return value
| Type | Description |
|---|---|
| string set |
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. A set containing results is returned if vectorization is used. |
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[]{0}, val[]{1} );
echo("Input {col,row}: ", val[], " Result: ", coord[], " (Expected: ", name[], ")" );
}
echo( "Vectorization: ", excel coordinates( {1,2,3}, {2,4,6} ) );
Output
Input {col,row}: {0,0} Result: A1 (Expected: A1)
Input {col,row}: {26,3} Result: AA4 (Expected: AA4)
Input {col,row}: {702} Result: {} (Expected: AAA)
Input {col,row}: {51,3} Result: AZ4 (Expected: AZ4)
Input {col,row}: {1} Result: {} (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: {} (Expected: ZZ)
Vectorization: {'B3','C5','D7'}