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
------------------------------
Original Message:
Sent: 11-12-2019 00:53
From: Pushpakumar Gnanadurai
Subject: Sort and Segregate text message
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
Original Message:
Sent: 11-11-2019 10:22
From: Eric Mohlman
Subject: Sort and Segregate text message
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
Original Message:
Sent: 11-11-2019 08:46
From: Pushpakumar Gnanadurai
Subject: 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
------------------------------