I use the calc below to format phone numbers.  However I can't work out why the "+" sign does not display when the format is recalculated.  I now get for example "61 3 9573 4822" when what I want is "+ 61 3 9573 4822".  

 

Can anyone set me right on this one?

 

 

 

TextFormatRemove ( MiddleWords ( no_de_telephone ; 1 ; Length ( If (Left ( no_de_telephone ; 4 )  = "+ 61" ; 

Let ( [

~input = Filter ( no_de_telephone ; "+0123456789" );

~mask = "f aa b cccc dddd"

];

Substitute (

~mask ;

[ "f" ; Left ( ~input ; 1 ) ];

[ "aa" ; Middle ( ~input ; 2 ; 2 ) ];

[ "b" ; Middle ( ~input ; 4 ; 1 ) ];

[ "cccc" ; Middle ( ~input ; 5 ; 4 ) ];

[ "dddd" ; Middle ( ~input ; 9 ; 4 ) ]

)

);

 

 

If (Left ( no_de_telephone ; 3 )  = "+ 1"  ; 

Let ( [

~input = Filter ( no_de_telephone ; "+0123456789" );

~mask = "f e aaa bbb cccc"

];

Substitute (

~mask ;

[ "f" ; Left ( ~input ; 1 ) ];

 

[ "e" ; Middle ( ~input ; 2 ; 1 ) ];

 

[ "aaa" ; Middle ( ~input ; 3 ; 3 ) ];

[ "bbb" ; Middle ( ~input ; 6 ; 3 ) ];

[ "cccc" ; Middle ( ~input ; 9 ; 4 ) ]

)

);

 

If (Left ( no_de_telephone ; 4 )  = "+ 33" ; 

Let ( [

~input = Filter ( no_de_telephone ; "+0123456789" );

~mask = "f aa b cc dd ee gg"

];

Substitute (

~mask ;

[ "f" ; Left ( ~input ; 1 ) ];

[ "aa" ; Middle ( ~input ; 2 ; 2 ) ];

[ "b" ; Middle ( ~input ; 4 ; 1 ) ];

[ "cc" ; Middle ( ~input ; 5 ; 2 ) ];

[ "dd" ; Middle ( ~input ; 7 ; 2 ) ];

[ "ee" ; Middle ( ~input ; 9 ; 2 ) ];

[ "gg" ; Middle ( ~input ; 11 ; 2 ) ]

)

);

 

Let ( [

~input = Filter ( no_de_telephone ; "+0123456789" );

~mask = "f aa bb ccc dd ee"

];

Substitute (

~mask ;

[ "f" ; Left ( ~input ; 1 ) ];

[ "aa" ; Middle ( ~input ; 2 ; 2 ) ];

[ "bb" ; Middle ( ~input ; 4 ; 2 ) ];

[ "ccc" ; Middle ( ~input ; 6 ; 3 ) ];

[ "dd" ; Middle ( ~input ; 9 ; 2 ) ];

[ "ee" ; Middle ( ~input ; 11 ; 3 ) ]

)

)))) ) ))

 

You only get a "+" if there was one to begin with; but that's really hard to tell with a calculation that shows how using Let() alone doesn't help.

 

Try this one; if it doesn't work exactly as you intend , it's pretty straightforward to tweak:

Let ( [

~tn = "+ 61 3 9573 4822" ; // no_de_telephone ;
~input = Filter ( ~tn ; "+0123456789" ) ;

l11 = Left ( ~input ; 1 ) ;
m21 = Middle ( ~input ; 2 ; 1 ) ;
m22 = Middle ( ~input ; 2 ; 2 ) ;
m33 = Middle ( ~input ; 3 ; 3 ) ;
m41 = Middle ( ~input ; 4 ; 1 ) ;
m42 = Middle ( ~input ; 4 ; 2 ) ;
m52 = Middle ( ~input ; 5 ; 2 ) ;
m54 = Middle ( ~input ; 5 ; 4 ) ;
m63 = Middle ( ~input ; 6 ; 3 ) ;
m72 = Middle ( ~input ; 7 ; 2 ) ;
m92 = Middle ( ~input ; 9 ; 2 ) ;
m94 = Middle ( ~input ; 9 ; 4 ) ;
m112 = Middle ( ~input ; 11 ; 2 ) ;
m113 = Middle ( ~input ; 11 ; 3 ) ;

~f = Case (
  Left ( ~tn ; 4 )  = "+ 61" ; 
  List ( l11 ; m22 ; m41 ; m54 ; m94 )  ;

  Left ( ~tn ; 3 )  = "+ 1"  ; 
  List ( l11 ; m21 ; m33 ; m63 ; m94 ) ;

  Left ( ~tn ; 4 )  = "+ 33" ; 
  List ( l11 ; m22 ; m41 ; m52 ; m72 ; m92 ; m112 ) ;

  List ( "+" ; l11 ; m22 ; m42 ; m63 ; m92 ; m113 ) 
 ) 
] ;

Substitute ( ~f ; ¶ ; " " )
)

 

I just tried this custom function from http://www.briandunning.com/cf/1459 here and it did the format that you were asking for.

 

HTH

 

Lee

 

p.s. I noticed that the plus doesn’t stick if you put the space in there +## # #### #### shows as +61 3 9573 4822 ,  and + ## # #### #### shows as 61 3 9573 4822

Thanks for all that.  Both of you.  So that has fixed that.  Thanks