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 FMPfile with German settings)
HISTORY:
20111004 [000000] 1.20 tseidler original number function
20120408 [095125] 1.21 TSW added numberexception ("00003" is not a number)
20120409 [122301] 1.22 TSW added commaseparation
20120411 [225457] 1.23 TSW removed zeropadding again
20120418 [210556] 1.30 TSW switched to _delimiter
20120419 [120433] 2.00 TSW aligned to other Isfunctions: IsNumber returns value if valid or nothing if false
20120419 [235920] 2.10 TSW caches file settings in $$IsNumberFormatAccepted on first run
20120422 [101156] 2.11 TSW fixed converting bug
20120918 [083745] 2.20 TSW eliminated one evaluation
20150831 [092700] 3.00 TSW renamed to Number_Value
20151021 [121200] 4.00 TSW complete rewrite
20180201 [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.