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...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
- Thanks a lot for your help!
------------------------------
Pushpakumar Gnanadurai
------------------------------
