SortableTextWithNumbers ( text )

Returns an alphanumeric string suitable for sorting mixed numbers and characters

Average rating: 4.4 (22 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

12.3b
123b
2b
0

Sample output:

00000000123000000000b
00000001230000000000b
00000000020000000000b
00000000000000000000

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

/*
SortableTextWithNumbers ( text ) recursive Custom Function
Returns an alphanumeric string suitable for sorting mixed numbers and characters
2015/06/09
2015/06/11 minor revision
Daniele Raybaudi
*/
Let ([
dec = Left ( 1/2 ; 1 ) ;
adj = Substitute ( text ; [ "/" ; dec ] ; [ " " ; ¶ ] ) ;
$countSTWN = $countSTWN + 1 ;
w =  GetValue ( adj ; $countSTWN ) ;
w =  Substitute ( TrimAll ( KanjiNumeral ( w ) ; 1 ; 1 )
; [ "一" ; 1 ]
; [ "二" ; 2 ]
; [ "三" ; 3 ]
; [ "四" ; 4 ]
; [ "五" ; 5 ]
; [ "六" ; 6 ]
; [ "七" ; 7 ]
; [ "八" ; 8 ]
; [ "九" ; 9 ]
; [ "〇" ; 0 ]
; [ " . " ; "." ]
; [ " " & dec & " " ; dec ]
; [ " " ; ¶ ]
) ;
w1 = GetValue ( w ; 1 ) ;
w2 = GetValue ( w ; 2 ) ;
w3 = GetValue ( w ; 3 )
];
Case(
$countSTWN ≤ ValueCount ( adj ) ;
Case (
not IsEmpty ( GetAsNumber ( w1 ) ) and not IsEmpty ( GetAsNumber ( w3 ) ) ; SerialIncrement ( "0000000000" ;  Int ( w1 )  ) & SerialIncrement ( "0000000000" ;  Truncate ( Mod ( w1 ; 1 )*10^10   ; 10 ) ) & w2 & SerialIncrement ( "0000000000" ;  Int ( w3 )  ) & SerialIncrement ( "0000000000" ;  Truncate ( Mod ( w3 ; 1 )*10^10   ; 10 ) ) ; // 1a1
not IsEmpty ( GetAsNumber ( w2 ) ) and Length ( w3 ) ; w1 & SerialIncrement ( "0000000000" ;  Int ( w2 )  ) & SerialIncrement ( "0000000000" ;  Truncate ( Mod ( w2 ; 1 )*10^10   ; 10 ) ) & w3 ; // a1a
not IsEmpty ( GetAsNumber ( w1 ) ) ; SerialIncrement ( "0000000000" ;  Int ( w1 )  ) & SerialIncrement ( "0000000000" ;  Truncate ( Mod ( w1 ; 1 )*10^10   ; 10 ) ) & w2 ; // 11a or 1aa
not IsEmpty ( GetAsNumber ( w2 ) ) ; w1 & SerialIncrement ( "0000000000" ;  Int ( w2 )  ) & SerialIncrement ( "0000000000" ;  Truncate ( Mod ( w2 ; 1 )*10^10   ; 10 ) ) ; // a11 or aa1
w1 // aaa
) & SortableTextWithNumbers ( text ) ;
Let ( $countSTWN = "" ; "" )
)
)

Returns an alphanumeric string suitable for sorting mixed numbers and characters.

Comments

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

SortableTextWithNumbers ( text )

Comments

Support this website.

Support this website.