54832_en-US

liliconcom

Disaggregate a text string in a data table

I am looking for a solution how to disaggregate a textstring in a data table. E.g. the text look like this:
"New: abedpk möckife Adv: pierjpkmdf"
I want to separate the string following the "New:" part into one separate column and the string following the "Adv:" part into another. The length of the strings between the starting keywords can be very different from cell to cell.

How can I do this? I tried some commands from the formula editor, but wasn't successful.
Thanks for your help in advance!

Markus

Re: Disaggregate a text string in a data table

Thanks for your proposal. It brought me to this idea:

 

1) I substituted "New:" and "ADV:" by "\§\" (because this sign is not appearing in the text)

2) made new columns for "NEW" and "ADV"

3) selected strings by this formula:

Trim( Word( 1, Trim( :AB ), "\§\" ) )

Trim( Word( 2, Trim( :AB ), "\§\" ) )

This gives me the possibility that the text between the delimiters can also have sentences or blanks

 

 

Re: Disaggregate a text string in a data table

Take a look at Cols > Utilities > Text to Columns

Re: Disaggregate a text string in a data table

This can be handled easily by using the Word() function.  

     Help==>Scripting Index==>Word

Here is a simple script that illistrates how it works.....Note the "-1" in the second usage of Word, indicating to start from the end of the string

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "First", character, formula( Word( 1, :yourstringcolumn, ":" ) ) );
dt << New Column( "Last", character, formula( Word( -1, :yourstringcolumn, ":" ) ) );