Discussions

Expand all | Collapse all

Sort and Segregate text message

  • 1.  Sort and Segregate text message

    Posted 11-11-2019 08:46
    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
    ------------------------------


  • 2.  RE: Sort and Segregate text message

    Posted 11-11-2019 10:23
    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
    ------------------------------



  • 3.  RE: Sort and Segregate text message

    Posted 11-12-2019 00:53
    Edited by Pushpakumar Gnanadurai 11-12-2019 00:55
    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
    ------------------------------



  • 4.  RE: Sort and Segregate text message

    Posted 11-12-2019 09:48
    Edited by Eric Mohlman 11-12-2019 09:49
    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
    ------------------------------



  • 5.  RE: Sort and Segregate text message

    Posted 11-12-2019 10:27
    Thanks a lot for your help!

    ------------------------------
    Pushpakumar Gnanadurai
    ------------------------------