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.
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 🙂
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:
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP