Hi, I'm trying to go from this: RecordData1aaa|eee|sss|ggg|xxx|2bbb|ccc|kkk|lll|3zzz|www|fff|hhh| To this: RecordDataaaabbbccc1aaa|eee|sss|ggg|xxx|1002bbb|ccc|kkk|lll|0113zzz|www|fff|hhh|000 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!

Parsing data into colulmn

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!

Data Investigation

Accepted answers

BrandonB

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.

All comments

BrandonB

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

grouping.yxzp

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!

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335