PointOrCommaSeparator_to_SystemSeparator ( _input ; _resultAsText__B ; _4to6withSepAsDecimal__B )

For numbers (with or without unit) coming from a text string, sometimes you don't know which decimal or thousands separator was used in the input. This CF converts the found decimal separators and thousands separators to the ones given by the system. The CF outputs either a text string that retains units or a number.

Be the first to rate this function Log in to vote

Udo Bösch - Show more from this author
Udo R. Bösch mipiano apps
http://mipiano.de

Sample input:

PointOrCommaSeparator_to_SystemSeparator ( "1,12" ; 0 ; 0 )
PointOrCommaSeparator_to_SystemSeparator ( "1.12 EUR" ; 0 ; 1 )
PointOrCommaSeparator_to_SystemSeparator ( "1,123 g" ; 1 ; 0 )
PointOrCommaSeparator_to_SystemSeparator ( "1,123 kg" ; 1 ; 1 )
PointOrCommaSeparator_to_SystemSeparator ( "12.123 EUR" ; 1 ; 0 )
PointOrCommaSeparator_to_SystemSeparator ( "12.123 USD" ; 1 ; 1 )

Sample output:

1.12 (e.g. in the US), 1,12 (in Germany)
1.12 (e.g. in the US), 1,12 (in Germany)
"1,123 g" (e.g. in the US), "1.123 g" (in Germany)
"1.123 kg" (e.g. in the US), "1,123 kg" (in Germany)
"12,123 EUR" (e.g. in the US), "12.123 EUR" (in Germany)
"12.123 USD" (e.g. in the US), "12,123 EUR" (in Germany)

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
• Purpose:
	› For numbers (with or without unit) coming from a text string, 
	  sometimes you don't know which decimal or thousands separator was used in the input.
	  This CF converts the found decimal separators and thousands separators to the ones given by the system.
	  The CF outputs either a text string that retains units or a number.

• Parameters:
	› _input : your string or number
	› _resultAsText :
			1 = the result is a text string that retains a given unit
			0 = the result is only the decimal number
	› _4to6withSepAsDezimal : (only affects 4- to 6-digit numbers)
			1 = the separator will be interpreted as decimal separator
			0 = the separator will be interpreted as thousands separator

• Limitations:
	› You must decide for your situation how 4- to 6-digit numbers containing a separator 
	  will be handled: Should the separator be interpreted as a decimal separator or as a thousands separator. 

• Author:
	› Udo Bösch | mipiano apps & design · u.boesch.dev@mipiano.de

• History:
	› 2025-10-19	Udo Bösch  ›  added handling of negative numbers
	› 2023-10-30	Udo Bösch  ›  created
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
*/

Let ( [
	¢InputIsNegative__B = Left ( Trim ( _input ) ; 1 ) = "-"
;	¢Input = If ( ¢InputIsNegative__B ; Middle ( _input ; 2 ; Length ( _input ) -1 ) ; _input )
;	¢Input = Substitute ( ¢Input ; [ "-" ; "00" ] ; [ "–" ; "00" ] ; [ "—" ; "00" ] )
;	¢InputLength = Length ( ¢Input )
;	¢Number = Filter ( ¢Input ; "-0123456789,.’" )
;	¢NumberLength = Length ( ¢Number )
;	¢PosComma = Position ( ¢Number ; "," ; ¢NumberLength ; -1 )
;	¢PosPoint = Position ( ¢Number ; "." ; ¢NumberLength ; -1 )

;	¢FoundDecSep = If ( ¢PosComma > ¢PosPoint ; "," ; "." )
;	¢FoundDecSepCount = PatternCount ( ¢Number ; ¢FoundDecSep ) 
;	¢FoundThsndSep = If ( ¢FoundDecSep = "," ; "." ; "," )
;	¢FoundThsndSepCount = PatternCount ( ¢Number ; ¢FoundThsndSep )
 
;	¢PosDecSep = Position ( ¢Number ; ¢FoundDecSep ; ¢NumberLength ; -1 )
;	¢LengthRest = ¢NumberLength - ¢PosDecSep
;	¢DecSep = Filter ( 3/2 ; ".," )
;	¢ThsndSep = If ( ¢DecSep = "." ; "," ; "." )
;	¢result = Case (
					Left ( ¢Input ; 1 ) = ¢FoundDecSep
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; "0" & ¢DecSep ] )
;
					¢FoundDecSepCount = 1 and ¢FoundThsndSepCount > 0
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; "___" ] ; [ ¢FoundThsndSep ; "|||" ] ; [ "___" ; ¢DecSep ] ; [ "|||" ; ¢ThsndSep ] )
;
					¢LengthRest = 2 and ¢FoundDecSepCount > 1 and ¢FoundThsndSepCount = 0
;						"??"
;
					¢LengthRest > 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢DecSep ] )
;
					_4to6withSepAsDecimal__B = 1 and ¢LengthRest = 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0 and ¢NumberLength ≥ 5
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢DecSep ] )
;
					¢LengthRest = 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0 and ¢NumberLength > 7
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢DecSep ] )
;
					_4to6withSepAsDecimal__B < 1 and ¢LengthRest = 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0 and ( ¢NumberLength < 5 )//ODER ¢NumberLength > 7 )
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢ThsndSep ] )
;
					¢LengthRest = 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢ThsndSep ] )
;
					¢FoundDecSepCount = 1 and ¢FoundThsndSepCount = 0
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢DecSep ] )
;
					¢FoundDecSepCount > 1 and ¢FoundThsndSepCount = 0
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢ThsndSep ] )
;
					¢LengthRest = 3 and ¢FoundDecSepCount = 1 and ¢FoundThsndSepCount > 1
;						Substitute ( ¢Input ; [ ¢FoundDecSep ; ¢ThsndSep ] )
;
					¢FoundDecSepCount = 0 and ¢FoundThsndSepCount = 0
;						¢Input
;
					/*else*/
						"?"
						)
]
; // Calculation:
Case (
	_resultAsText__B = 1 and ¢InputIsNegative__B
;		"-"& ¢result
;
	_resultAsText__B = 1
;		¢result
;
	/*sonst*/
		GetAsNumber ( ¢result )
)
)

• PURPOSE:
For numbers (with or without unit) coming from a text string, sometimes you don't know which decimal or thousands separator was used in the input.
This CF converts the found decimal separators and thousands separators to the ones given by the system.
The CF outputs either a text string that retains units or a number.

• PARAMETERS:
_input : your string or number

_resultAsText :
1 = the result is a text string that retains a given unit
0 = the result is only the decimal number

_4to6withSepAsDezimal : (only affects 4- to 6-digit numbers)
1 = the separator will be interpreted as decimal separator
0 = the separator will be interpreted as thousands separator

• LIMITATIONS:
You must decide for your situation how 4- to 6-digit numbers containing a separator will be handled: Should the separator be interpreted as a decimal separator or as a thousands separator.

Comments

		Naio Oct 17, 2025
Useful but it doesn't seem to consider negative numbers

		Udo Bösch, Udo R. Bösch mipiano apps Oct 19, 2025
Thank you for pointing that out, Naio. I have adjusted the function. However, I noticed another problem: in some regions, a space or an apostrophe is used as a thousand separator. I only found this out recently. I will incorporate this at the next opportunity.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

PointOrCommaSeparator_to_SystemSeparator ( _input ; _resultAsText__B ; _4to6withSepAsDecimal__B )

Comments

Support this website.

Support this website.