Pivot Table Text Grouping by the First 3 Characters

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
Hi guys, I need a help with the Pivot Table please, I need to group text fields automatically by the first 3 characters, how could I do that? Noting that I need to do this in the same workbook because I came across some solution but it needed to create new workbook for the Pivot Table. I do not want to proceed with manipulating the raw data as well, I need to do this through the Pivot Table itself please.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sounds like a perfect use for the Calculated Field feature in a pivot table. Formula for the field would be something like LEFT(Customer,3).
 
Upvote 0
But how to use this feature here exactly? And it helps with the grouping purpose?
 
Last edited:
Upvote 0
add table to DataModel
create new column, eg. =left([Surname],3)
create PivotTable from there
put the new column (with three letters) as first into the ROWS area
the rest is up2you

example

screenshot-52.png
 
Last edited:
Upvote 0
But I don't want to added any extra column to the raw data for some project consideration
I sense that there is another way to do it through the Pivot Table itself.
 
Upvote 0
I didn't add any column to the raw data, I added column in DataModel
re-read my post

D'ya have a PowerPivot? or better: what is your Excel version?
 
Last edited:
Upvote 0
I don't get it, what do you mean by DataModel
I use Office 2016

Imagine this as a raw data for instance:
AGU A 10
AGU T 25
AGU X 57
ALX A 10
HLP C 29
HLP A 5
 
Last edited:
Upvote 0
this is 2016 and as you can see DataModel exist

screenshot-53.png


now imagine the result from you example without DataModel :LOL:

raw dataPivotTable
threeonenumberthreeoneSum of number
AGUA
10​
AGUA
10​
AGUT
25​
T
25​
AGUX
57​
X
57​
ALXA
10​
ALXA
10​
HLPC
29​
HLPA
5​
HLPA
5​
C
29​

pivottablefields.jpg
 
Upvote 0
I guess, I managed with the DataModel tool.
I did not see that it is an Add-in before.
And it's my first time to hear about this tool.
But it's cool, it seems to me that it's got more power than that.
Gotta to check it out more later.

Thank you! You were very helpful.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top