Number_Value ( _var )
return a valid number according to regional setting or nothing if invalid
Average rating: 4.6 (25 votes) Log in to vote
|
Rewolfer - Show more from this author
http://rewolfer.de |
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
/*===============================================
FORMAT:
Number_Value ( _var )
PURPOSE:
returns a valid number according to regional setting/ global var $$NumberFormatAccepted or nothing
PARAMETER:
_var type: unknown
RESULT:
valid number in regional setting or nothing if invalid
DEPENDENCY:
-
NOTE:
assuming numbers (leading and trailing zeros/ spaces are ignored)
EXAMPLE:
Number_Value ( ".445" ) => 0,445 (in a FMP-file with German settings)
HISTORY:
2011-10-04 [000000] 1.20 tseidler original number function
2012-04-08 [095125] 1.21 TSW added number-exception ("00003" is not a number)
2012-04-09 [122301] 1.22 TSW added comma-separation
2012-04-11 [225457] 1.23 TSW removed zero-padding again
2012-04-18 [210556] 1.30 TSW switched to _delimiter
2012-04-19 [120433] 2.00 TSW aligned to other Is-functions: IsNumber returns value if valid or nothing if false
2012-04-19 [235920] 2.10 TSW caches file settings in $$IsNumberFormatAccepted on first run
2012-04-22 [101156] 2.11 TSW fixed converting bug
2012-09-18 [083745] 2.20 TSW eliminated one evaluation
2015-08-31 [092700] 3.00 TSW renamed to Number_Value
2015-10-21 [121200] 4.00 TSW complete rewrite
2018-02-01 [105700] 4.10 TSW "+" return debugged
DEVELOPER:
TS Wolf
=================================================*/
Let ([
// first run sets $$NumberFormatAccepted to either "." or ","
_tmp = Case ( IsEmpty ( $$NumberFormatAccepted ) ; Let ( $$NumberFormatAccepted = Case ( GetAsNumber ( "1,2" ) = 12 ; "." ; "," ) ; "" )) ;
_var = Case ( $$NumberFormatAccepted = "," ; Substitute ( _var ; "." ; "," ); Substitute ( _var ; "," ; "." )) ;
_cleanExp = Filter ( _var ; "-+eE0123456789 " & $$NumberFormatAccepted ) ;
_cleanNum = Filter ( _cleanExp ; "-+0123456789" & $$NumberFormatAccepted ) ;
_cleanDigit = Filter ( _cleanNum ; "0123456789" ) ;
_countE = PatternCount ( _cleanExp ; "e" ) ;
_countSign = PatternCount ( _cleanExp ; "-" ) ;
_countDec = PatternCount ( _cleanNum ; $$NumberFormatAccepted )
];
Case (
_var = "True" ; 1 ;
_var = "False" ; 0 ;
IsEmpty ( _cleanDigit ) ; "" ;
// simple number
_countE = 0 and _var = _cleanNum and _countDec < 2 and Case ( _countSign = 0 ; True ; _countSign = 1 and Left ( _cleanNum ; 1 ) = "-" and Length ( _cleanNum ) > 1 and Right ( _cleanNum ; 1 ) <> 0 ; True ; False ) ; _var ;
// exponential
_countE = 1 and _var = _cleanExp and _countDec < 2 ;
Let ([
_split = Substitute ( _var ; "e" ; ¶ ) ;
_splitCount = ValueCount ( _split ) ;
_base = Trim ( GetValue ( _split ; 1 )) ;
_baseSignCount = PatternCount ( _base ; "-" ) ;
_exponent = Trim ( GetValue ( _split ; 2 )) ;
_exponentSignCount = Length ( Filter ( _exponent ; "+-" ))
];
Case (
_splitCount = 2 and
Case ( _baseSignCount = 0 ; True ; _baseSignCount = 1 and Left ( _base ; 1 ) = "-" ; True ; False ) and
Case ( _exponentSignCount = 0 ; True ; _exponentSignCount = 1 and ( Left ( _exponent ; 1 ) = "-" or Left ( _exponent ; 1 ) = "+" ) ; True ; False ) ;
//result
_base * 10 ^ _exponent )
)
)
)
Comments
 |
Gilles Plante May 3, 2018 |
|
| What is the difference between this CF and GetAsNumber( var ) from plain FileMaker ? | ||
|
Rewolfer May 3, 2018 |
|
| GetAsNumber ( "1,2" ) will result in nothing on an American system. But obviously it is a metric number. So with this function you can define your target seperator (otherwise it will be checked with local system settings) and convert to this target system setting. I needed this function to import numbers from various regions of the world with no knowledge what seperator was used. |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.