B4P Reference

Function Names

excel column

Description

This function converts an Excel column designtor (combination of 1-3 letters) to a column number.
Note: Excel column A translates to 0, B to 1, Z to 25, AA to 26, etc.

Vectorization: This function supports vectorization in the 1st 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 function parameter

Parameter count

Parameters

No.	Type	Description
1 input	string set	Excel column specifier Valid value are: A, B, ..., Z, AA, AB, ZZ, AAA, AAB, etc. Do not put spaces between the letters. Letters after spaces will be ignored. Numbers (e.g. row numbers) will also be ignored. Optionally, use vectorization by putting multiple values into a (nested) set.

Return value

Type	Description
numeral set	Column number Valid column numbers begin with 0. -1 is returned for invalid column specifiers, e.g. missing letters. If Vectorization is used, then a set containing multiple results is returned.

Examples

       for all ( { A, B5, Z23, AA1, ZZ, AAA, AAB, XFD, C A, '123 D', '123', ' B', '...' }, c[] )

       {

               echo(c[],": ", excel column ( c[] ) );

               // Note: -1 is returned if no appropriate letter is recognized.

       }

Output

A: 0
B5: 1
Z23: 25
AA1: 26
ZZ: 701
AAA: 702
AAB: 703
XFD: 16383
C A: 78
123 D: 3
123: -1
 B: 1
...: -1

Try it yourself: Open LIB_Function_excel_column.b4p in B4P_Examples.zip. Decompress before use.