TransWikia.com

Split data into columns from Pivot Table

Super User Asked on November 3, 2021

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.

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 🙂

2 Answers

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 :)

Answered by LeRouteur on November 3, 2021

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:

enter image description here

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.

Answered by FlexYourData on November 3, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP