Multiple values in one cell - Pivot Table

[htremblay]

Hello,
First post here, but I have gotten a ton of help already, so thanks to everyone for all this great information!

I have a "Lot number" column where I list a lot number, and I have a Pivot Table that generates a graph of how many times each lot number is listed. I use a pivot table because I cannot list all of the possible lots. This is working fine.

However, I now have instances where the "Lot number" column contains multiple lot numbers, separated by commas. How can I my graph to work properly and still plot the number times each lot is listed? I was thinking of separating the multiple lots into separate columns, but I cannot (don't want to) use the "text to column". Is there a formula that could do it?

Hopefully I am clear in what I am trying to accomplish.
Thank you.

 

[wigi]

I was thinking of separating the multiple lots into separate columns, but I cannot (don't want to) use the "text to column".

What do you have against this builtin functionality of Excel?

I agree that a function is calculating on the fly, but there are disadvantages too of using formulas.

 

[htremblay]

My spreadsheet is a database that various employees can populate. The file is protected and people dont have access to that menu. I am looking for something that would be done automatically in the background, not an operation that has to be done each time new data is entered...

 

[wigi]

Have you considered, when desiging the application / template, that numbers could be several filled in in one cell? This is bad spreadsheet design.

Why not several lines with only one value per cell? In the background, a copy action could help the users filling in the file.