Hello, I'd like to be able to hold simple tags within my data that can be replaced in a calculated field with a full anchor on export:

'domain.co.uk'

and replace with

'domain.co.uk'

Any help would be greatly appreciated.

Try something like =

Let ( [

open = Position ( text ; "[url]" ; 1 ; 1 ) ; 

close = Position ( text ; "[/url]" ; open ; 1 ) ;

url = Middle ( text ; open + 5 ; close - open  - 5) ;

bef = Left ( text ; open - 1 ) ;

aft = Right ( text ; Length ( text ) - close - 5 ) 

] ;

Case ( open ; 

bef & "" & url & "" & aft ;

text

)

)

This will handle the first URL in text. If text can contain multiple URL's, you will need a recursive custom function or a script to further process the aft part.

Many thanks, that worked a treat, I doubt we'll have more than once instance in a column - but... is there a simple way of doing this recursively/nesting it? Thanks again R

To do it recursively, you need to create a custom function.

Say you name your function process_url_tag(text), then you could modify the code to be something like

Let ( [

open = Position ( text ; "[url]" ; 1 ; 1 ) ; 

close = Position ( text ; "[/url]" ; open ; 1 ) ;

url = Middle ( text ; open + 5 ; close - open  - 5) ;

bef = Left ( text ; open - 1 ) ;

aft = Right ( text ; Length ( text ) - close - 5 ) 

] ;

Case (

(open > 0) and (close = 0); "Syntax Error: Missing close url tag";

(open = 0) and (close > 0); "Syntax Error: Missing open url tag";



open ; 

bef & "" & url & "" & process_url_tag(aft) ;

text

)

) 

If you lack the tools to create a custom function, you could apply the original calculation repetitively in a script using a Set Field step. Place a handful of identical Set Field or Replace Field Contents steps in a row to handle the maximum number of tags you expect to find, or put it in a loop designed to exit when no more pairs are found.

Edited November 23, 200916 yr by Guest

It's not too difficult to make it a custom function:

TransformURLs ( text ) =

Let ( [

open = Position ( text ; "[url]" ; 1 ; 1 ) ; 

close = Position ( text ; "[/url]" ; open ; 1 ) ;

url = Middle ( text ; open + 5 ; close - open  - 5) ;

bef = Left ( text ; open - 1 ) ;

aft = Right ( text ; Length ( text ) - close - 5 ) 

] ;

Case ( open ; 

bef & "" & url & "" & TransformURLs ( aft ) ;

text 

)

) 

Thanks chaps, That worked a dream, if you ever find yourself in the Lord Raglan (Kent, UK) I'll buy you a pint or two!