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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,487
Messages
5,601,961
Members
414,486
Latest member
Darkai

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
Top