ExcelColumn ( )

Function stats

Average user rating
37
326
9999
Support
FileMaker 8.0 +
Date posted
27 March 2012
Version
1
Recursive function
No

Author Info
 Fabrice

70 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

ExcelColumn  ( _column )


Parameters

_column  a string or an integer


Description

Tags:  Format   Dev  

Converts :
- an Excel column header into a number (BR -> 70)
- a number into an Excel column header (70 -> BR)

Limited to ZZ (702)

Examples

Sample input

ExcelColumn ( "BR" )
ExcelColumn ( 70 )


Sample output

70
BR

 

Function code

/* ExcelColumn ( _column )

by Fabrice Nordmann, 1-more-thing

http://www.1-more-thing.com
http://www.twitter.com/1morethingtweet

Converts :
- an Excel column header into a number (BR -> 70)
- a number into an Excel column header (70 -> BR)

Limited to ZZ (702)


*/


Case (
    Filter ( _column ; 1234567890 ) = _column and GetAsNumber ( _column ) <= 702 ;

// Number -> Column
Let ([
    _a = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
    _n = GetAsNumber ( _column ) ;
    _d = Div ( _n ; 26 ) ;
    _m = Mod ( _n ; 26 ) ;
    _p = Case ( _m ; _d ; _d - 1 ) ;
    _f = Case ( _p ; Middle ( _a ; _p ; 1 )) ;
    _s = Middle ( _a ; _n - _p*26 ; 1 )
];
_f & _s
);



// Column -> Number
Length ( _column <= 2 );

Let ([
    _a = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
    _f = Middle ( _column ; 1 ; 1 ) ;
    _s = Middle ( _column ; 2 ; 1 ) ;
    _pf = Position ( _a ; _f ; 1 ; 1 ) ;
    _ps = Position ( _a ; _s ; 1 ; 1 )
];
    Case ( not _ps ; _pf ; _pf *26 + _ps )
)
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/326

    Prototype: ExcelColumn( _column )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 27 March 2012
    Version: 1

*/
// ===================================

 

 

 

 

 

 

 

Top Tags

Text Parsing  (31)
Date  (26)
List  (25)
Format  (23)
XML  (22)
Sql  (18)
Dev  (17)
Debug  (14)
Text  (13)
Variables  (12)
Interface  (12)
Layout  (11)
Filter  (10)
Design  (9)
Array  (7)
Layout Objects  (7)