Split data into columns from Pivot Table

Question

I have an Excel worksheet who contains a Pivot Table. I have to split the data into 6 columns in the same worksheet as the Pivot Table.

For example, I want

m5.16xlarge-RHEL 7-Messaging|Preview-24/5-Maison

to get splitted in 6 columns, each column with a word (before the dash).

Thank's in advance :)

Answer 1

Splitting data into new columns within the pivot table is not possible in an easy or user-friendly way.

It will be better to use text-to-columns within the source data using "-" as the separator character.

First, ensure that you have enough empty columns immediately to the right of your VM column to contain the maximum number of separated pieces. So, if you expect 6 columns at the end, you will need 5 empty columns to the right of the VM name.

Next, select the VM list and use Data>Data Tools>Text to Columns

Selected "Delimited" on step 1, then configure step 2 like this:

You can optionally configure each column's data type and other options on step 3. Otherwise, hit Finish to separate the data into the columns.

Answer 2

I used a formula to split the data from the Pivot Table in each cell. Here's the formula :

=TRIM(MID(SUBSTITUTE($A3,"~",REPT(" ",LEN($A3))),(COLUMN()-4)*LEN($A3)+1,LEN($A3)))

You'll just have to change the sign (the "~") and replace the cell ($A3) with the cell you want. Then, just stretch the formula to cells and it works :)