How to calculate this?

[philipcaplan]

I have in an FMPro12 table a field containing data which is always text, each word separated by dashes (they are parts of URL's).

Some entries will be a single word (so no dashes); others may be 2, 3 or more words (always separated by dashes).

There will never be a 'space' in the field's data.

 

Typical contents might be for example:

entertainment

european-holidays

womens-jewelry-and-accessories

 

Can some kind person give me the Calculation I can use to:

(1) replace each 'dash' with a 'space'

(2) then uppercase the first letter only of each word (ie, initial capitals)

(3) and replace any occurrence of the word 'and' with '&'

 

Many thanks in anticipation.

[Wim Decorte]

let(

[

 

_string = "womens-jewelry-and-accessories" ;

_cleanString = Substitute ( _string ; [ "-" ; " " ] ; [ " and " ; " & " ] ) ;

_properString = Proper ( _cleanString )

 

];

 

 

_properString

 

 

)

[Lee Smith]

Use the substitute function. If your field = This A How To & This & That.

 

Proper (Substitute ( yourField ; 

[“-“; “ “ ];

[“and” ; “&”] 

)

)

I noticed that Wim is up early today and posted a Let, here is another way using the  Substitute function.

 

Lee

[philipcaplan]

Thanks, Lee.  That worked perfectly.

 

And thanks for your reply Wim, although I must admit I found Lee's explanation easier to follow!

 

Thanks to you both, and to FMForums for coming up trumps again!

 

Philip

[Lee Smith]

Hi Philip,

 

The Let Function is easier to follow than the “if” or “Case” most of the time.

 

I changed some of the words of Wim’s calculation to see if it would help you understand it better

 

Let ( [

text = yourField ; //equals the _string by Wim.

cleanString = Substitute ( text ; [ "-" ; " " ] ; [ " and " ; " & " ] ) ; // equals the same as my Substitute

newText = Proper ( cleanString ) //Proper is a Function in FileMaker.

] ;  

 

newText

) 

[Wim Decorte]

using the let makes it somewhat easier to troubleshoot, by asking of the various part if it does not seem to work.  So instead of returning "newText" you could return "cleanString" and see what it did....