pivot table - make Region 2A just Region 2?

stirlsa

Board Regular
Joined
Sep 17, 2004
Messages
216
I am basing a pivot table on a spreadsheet that has a column called Region/Unit. Here are the fields for the column: 1A, 1B, 2A, 2B, 3A, 3B, 3C, 3D, 4A, 4B, 4C, 4D, 5A, 5B.

I want to use region as a field in my pivot table but do not want to have the letter show up, just have Region 1, 2, 3, 4, and 5.

Is there a way to do this or do I need to create a new column just to show the region only?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi,

Without adding a new field, it can be done using SQL. Such as, if the data has a defined name "MyData". From a separate, new workbook start the pivot table wizard and take the external data option at the first step. Follow the wizard and at the last step take the option to edit in MS Query. Hit the SQL button and edit the text as below. OK to enter the revised SQL, see the revised dataset, hit the 'open door' icon to exit MS Query & complete the pivot table. If you like, the completed pivot table can be moved into the original data workbook.

HTH, Fazza

Code:
'before the change
SELECT Region, `other field name`, `etc etc`
FROM MyData
 
'after the change
SELECT Left(Region, 1) AS [Region], `other field name`, `etc etc`
FROM MyData
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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