To add data to separate columns

[#1]

Dear All,

I would like to ask you for help. I am loading this table from .xls:

ID	Text
1	…text...Name1….text….Name2….text…..Name3….
2	…text...Name1….text….Name2….text…..Name3….
3	…text...Name1….text….Name2….text…..Name3….
4	…text...Name1….text….Name2….text…..Name3….
…	…

I do not know, how I can determine & divide Name1/Name2/Name3 from string Text and add to separate columns. The expected result should be:

ID	Text	NewColumn1	NewColumn2	NewColumn3
1	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
2	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
3	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
4	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
…	…	…	…	…

Thank you in advance, James

[#2]

can u post sample data for text...Name1.....text....Name2 ?

[#3]

Of course, here it is an example:

***text text text text 26-Aug-2011 08:57 (GMT+2:00) added by Name1 text text text***text text text text 27-Aug-2012 01:45 (GMT+2:00) added by Name2 text text text***text text text text 12-Aug-2011 03:12 (GMT+2:00) added by Name3

Thank you, James

[renjithpl]

as per your text, you can try for

Name1

= subfield(trim(subfield(Text, 'added by', 2)), ' ', 1)

Name2

=subfield(SubField(Text, ' added by ', 3), ' ', 1)

Name 3

=SubField (Text, ' added by ', 4)

You can try accoring your string,

hope this helps

[#5]

Hi renjithpl,

thanks for answer, this is probably the right way.

However, i have not noticed, that there should be a lot of names in the string and they are composed from first name and last name [John Doe], so this solution is not the "best fit" for me.

Please see the original loaded data:

14-Dec-2011 10:41 (GMT+1:00) added by John Doe 1 (PID-004933):

TEXT TEXT TEXT

*************************************************************************************

14-Dec-2011 09:49 (GMT+1:00) added by John Doe 2 (PID-003104):

TEXT TEXT TEXT

*************************************************************************************

12-Dec-2011 15:03 (GMT+1:00) added by John Doe 3 (PID-003405):

TEXT TEXT TEXT

*****************************************************************************************

02-Dec-2011 08:46 (GMT+1:00) added by John Doe 4 (PID-003164):

TEXT TEXT TEXT

************************************************************************************

and more…

[Gysbert_Wassenaar]

Try the textbetween function.

trim(textbetween(Text,'added by','(PID')))

[#7]

Hello Gysbert,

I have tried function textbetween, it works perfectly, but unfortunately only for first name in the string..

James

[Gysbert_Wassenaar]

Oh, I thought you had more lines of text, not one long string. If you have a fixed number of names you can add an index to textbetween as second parameter to get the n-th match. Otherwise I'd first use subfield to create seperate records.