Forum Discussion

PushpakumarGna1's avatar
PushpakumarGna1
Qrew Assistant Captain
5 years ago

Sort and Segregate text message

Hi All,

Can some help me to split a column into two columns 

N|1|04-29-2013 ; N|2|06-11-2015 ; N|3|07-27-2017 ; N|5|04-05-2020 ; Y|1|04-29-2013 ; Y|2|04-29-2014 ; Y|3|04-29-2015 ; Y|4|02-23-2019

I want to convert to 
N|1|04-29-2013 ; N|2|06-11-2015 ; N|3|07-27-2017 ; N|5|04-05-2020 ;  ---> to column N
 Y|1|04-29-2013 ; Y|2|04-29-2014 ; Y|3|04-29-2015 ; Y|4|02-23-2019 ----> to column Y

Thanks

------------------------------
Pushpakumar Gnanadurai
------------------------------
Formulas and functions

4 Replies

Sort By
  • EricMohlman's avatar
    EricMohlman
    Qrew Cadet
    5 years ago
    Pushpakumar,

    If you create two formula - text type fields, you can enter these formulas into each:
    Trim(Left([TextFieldName],"Y"))
    Trim(NotLeft([TextFieldName],"Y"))

    Those will split your current text field into the two sets of information that you want.

    ------------------------------
    Eric Mohlman
    ------------------------------
    • PushpakumarGna1's avatar
      PushpakumarGna1
      Qrew Assistant Captain
      5 years ago
      Hi Eric , 

      Thanks a lot for the reply. But i have another condition 

      ALL|1|04-29-2013;ALL|4|04-29-2017;    N|1|04-29-2013 ; N|2|06-11-2015 ; N|3|07-27-2017 ; N|5|04-05-2020 ;  Y|1|04-29-2013 ; Y|2|04-29-2014 ; Y|3|04-29-2015 ; Y|4|02-23-2019;

      Now, i want to throw this into three column , N_text , Y_text and ALL_text. the order can be jumbled and sometimes any of the parameter can be null i.e i can have either N or Y or ALL or Y or Just Y

      Can you help me on this

      ------------------------------
      Pushpakumar Gnanadurai
      ------------------------------
      • EricMohlman's avatar
        EricMohlman
        Qrew Cadet
        5 years ago
        Pushpakumar,

        With that updated information, here's an example formula to gather all of the "ALL" options from your list in any order that they occur in:
        List(";",
        If(Left(Part([TextFieldName],1,";"),1)="A",Part([TextFieldName],1,";")),
        If(Left(Part([TextFieldName],2,";"),1)="A",Part([TextFieldName],2,";")),
        If(Left(Part([TextFieldName],3,";"),1)="A",Part([TextFieldName],3,";")),
        If(Left(Part([TextFieldName],4,";"),1)="A",Part([TextFieldName],4,";")),
        If(Left(Part([TextFieldName],5,";"),1)="A",Part([TextFieldName],5,";")),
        If(Left(Part([TextFieldName],6,";"),1)="A",Part([TextFieldName],6,";")),
        If(Left(Part([TextFieldName],7,";"),1)="A",Part([TextFieldName],7,";")),
        If(Left(Part([TextFieldName],8,";"),1)="A",Part([TextFieldName],8,";")),
        If(Left(Part([TextFieldName],9,";"),1)="A",Part([TextFieldName],9,";")),
        If(Left(Part([TextFieldName],10,";"),1)="A",Part([TextFieldName],10,";")),
        If(Left(Part([TextFieldName],11,";"),1)="A",Part([TextFieldName],11,";")),
        If(Left(Part([TextFieldName],12,";"),1)="A",Part([TextFieldName],12,";"))
        )

        That same pattern could be extended for the full amount of entries you anticipate could be in your data set. For the other two formula fields, change the A to either Y or N depending on which option you want to collect

        ------------------------------
        Eric Mohlman
        ------------------------------