Parsing data into colulmn

[Derek-]

Hi,

I'm trying to go from this:

Record	Data
1	aaa|eee|sss|ggg|xxx|
2	bbb|ccc|kkk|lll|
3	zzz|www|fff|hhh|

To this:

Record	Data	aaa	bbb	ccc
1	aaa|eee|sss|ggg|xxx|	1	0	0
2	bbb|ccc|kkk|lll|	0	1	1
3	zzz|www|fff|hhh|	0	0	0

I use the formula to create new column with if-then statement:

IF Contains([Data],"aaa") THEN 1 ELSE 0 ENDIF

It works, but slow, just wondering if there's a better way to do this?

Also, text to column doesn't work for this purpose.

Thanks much!

[BrandonB · Accepted solution]

What about a text to columns with split to rows selected followed by a summarize tool where you do a group by record ID, Group by data, and then count data. Then cross tab the data column to be your new headers and the count as your values with the group by record ID. This would get every data value as a column header and the count of each for each record underneath.

If it isn’t a consistent delimiter then it would be helpful to know what kind of data that you are working with to extract the patterns.

[JordyMicheal]

Just attached what @BrandonB was talking about.

Really good solution if the "|" is always the delimiter; if not just use regex with "." and Split to Rows.

Thanks

[Derek-]

Thanks, Brandon!

I actually did it a little differently, text to columns, then transpose (key: record; data fields: all data columns), then cross tab. Works great. Thanks a lot!