Text to columns and pivot table possible?

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi Everyone -


    I have an problem that I haven't been able to figure out and I'm hoping someone can help. I have an excel file with roughly 200K+ rows. I'm trying to run a pivot on the data to understand what upgrades customers have purchased. The column which shows the upgrades is comma delimited where the data is in the following format:


    upgrade 1 | upgrade 2 | upgrade 3
    Upgrade 1
    upgrade 2 | Upgrade 3
    upgrade 3


    I initially tried running a text 2 columns then using a pivot table, but the data comes out extremely messy. I then tried to just not use text to columns, but then the data is at an aggregated level where it isn't counting each unique occurrence ( for example, upgrade 1 should be counted 2x in the above example - not once).


    Is there a way to use text to columns and use a pivot table? If not, what other options can I use?

    Re: Text to columns and pivot table possible?


    Without seeing exactly what you have I would think that you need to separate the data then combine resulting columns into one. To use a pivotTable thought there must be other data so you need to attach an example workbook.

    Re: Text to columns and pivot table possible?


    Wow - Herbds


    Thanks - I have Excel 2010 at home. Do I just use text to columns and run the pivot on the texts to column table?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login