Choose Language Hide Translation Bar
Highlighted
markus
Level I

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
markus
Level I

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

 

 

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

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, ":" ) ) );
Jim
Highlighted
john_madden
Level V

Re: Disaggregate a text string in a data table

Take a look at Cols > Utilities > Text to Columns

Highlighted
markus
Level I

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

 

 

View solution in original post

Article Labels

    There are no labels assigned to this post.