Multiple data in one cell?

[New Daddy]

Is there any way to place multiple, delimiter-separated data in one cell?


For example, I want to put the attributes of a transaction in a single cell but the data in the cell to be treated not as one chunk but multiple data.
For example, for the attributes of a transaction, I want to denote lump-sum vs. installments, on-line vs. offline, referral vs. regular, etc.

I could do this by creating separate columns for each attribute, but I would save time and space if I could put them together in one cell. At the same time, I want Excel to recognize the data as multiple entries, rather than one chunk of text, so that a pivot table can pick them up separately.

Is this a possible idea?

[BobBridges]

Other people may have different opinions about this, but my own answer is that it's possible but usually inadvisable.

The "possible" part is that you can save three values as a comma-delimited string, eg "14000,online,REGULAR". Other cells that need to handle one of those values could use the FIND function to figure out that there's a comma in position 6, and then convert the first five characters into a number and calculate with it. You could write your own worksheet functions, too, to pick out the 1st, 2nd or 3rd value in a cell.

But the "inadvisable" part is that it's a lot more complicated to do that. I've been tempted to it myself, several times, but each time I end up deciding that the saved space isn't worth the extra effort.

Maybe someday the good folks at Microsoft will invent a feature in Excel that lets you invent additional properties in a single cell. But I'm betting they've already thought about it and decided "nah; that's what multiple cells are for".

[New Daddy]

Thanks for the opinion.

My purpose is not just to save space; it'd easier to create the kind of pivot table that I envision if I could place multiple data in one cell.

For example, in the example given above, I could easily create a pivot table that counts different transaction attributes per customer. I'm not sure if I can achieve a similar feat with one pivot table if the different attributes (i.e., payment type, online/offline, referral/regular) were placed in separate columns. (You can create three pivot tables based on each attribute and combine them after the fact, but that will take time and energy.) Hence my wish for multiple data entry in a single cell.

[Pecoflyer]

Perhaps post a sample of your data and an example of expected results. ( alos the kind of PT you want)

[C J Squibb]

You could enter the data into three different columns, then merge them into a text string in a fourth column, using the CONCATENATE function, for use in the pivot table if that gives you the results you want.