7 Replies Latest reply: Oct 25, 2012 4:41 AM by Gysbert Wassenaar RSS

    To add data to separate columns

      Dear All,

       

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

       

      IDText
      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:

       

      IDTextNewColumn1NewColumn2NewColumn3
      1…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
      2…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
      3…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
      4…text...Name1….text….Name2….text…..Name3….Name1Name2Name3

       

      Thank you in advance, James

        • Re: To add data to separate columns
          Lav Jain

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

            • Re: To add data to separate columns

              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

                • Re: To add data to separate columns
                  renjith pl

                  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

                   

                   

                   

                   

                   

                   

                    • Re: To add data to separate columns

                      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…