How to pull part of a pivot table field

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
I'm working in a pivot table in Excel 2010 with a field called 'GLNum' that holds a General Ledger Number which looks like this: '101-4567-999'. I'd like to create some sort of calculation in the pivot table which will return the first 3 characters of the GLNum. I tried to do this as a Calculated Field by entering left(GlNum,3) but this returns '0' for all entries. A Calculated Item doesn't work either as that only allows you to manipulate actual dimension values, like adding 5 different GLNums together as a group.

Any ideas on how I can accomplish this?

There are reasons why I want to do this in the pivot table and not in the underlying data which go beyond the scope of this posting. Thanks for any suggestions.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi,
please clarify where you would like your reduced GLNum to be placed, I take it that it should be in the Rows Labels right?
Calculated Field or Items will be placed ONLY in ∑Values...
Thanks to advise as your post shows that you already have a possible answer that you wouldn't use.
 
Last edited:

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
Thanks for your response. Yes, sorry, I should have said that I want to put the new item on the Row Labels. I'm wondering if there's some pivot table add-in that would do this.
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Yeah no problem, I am not sure about add-in, but calculated and items are not eligible for Report Filter, Row Labels or Column Labels.
Maybe a vba would be able to tweak the data... (not sure).
Making your raw data a table and adding a "left" would solve your issue but you stated that this possibility wasn't one.
Sorry for not being able to assist you further.
 

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74

ADVERTISEMENT

Thanks. I'm interested if anyone has any vba that would accomplish this. In googling around I can't seem to find anything.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi,

briefly, from a new workbook (CTRL-N) set up the pivot table (ALT-D-P) using the choose external data option at the first step. follow the wizard to the end and choose to edit in MS Query. Add the field into the SQL. Complete the pivot table. SQL like "SELECT LEFT(GLNum, 3) AS [new_field_name], other fields FROM your data"

I'll find a link to an old thread with a fuller description of setting it up. or please google as I've lots of old posts that explain

regards
 

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
Thanks for your efforts. However, if I choose 'external data source' I do not get a wizard that guides me thru a process which then allows me to choose 'Edit in MS Query'. I get the 'Create Pivot Table' screen. Then I choose 'Connection Properties' to choose the Excel file. Once I've chosen that file I'm back at the same screen. When I hit 'OK' the pivot table is created. And that's it.

If I try to get to MS Query by then clicking inside the pivot table and choosing 'Properties', the 'EDIT QUERY' option is greyed out. So I can't go that route either. FYI, I'm using Excel 2010.
 

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404
Hi Rhino !

Why not add a column in your source data, and use this function, pulled down till end:

Code:
=LEFT(Cell Reference, 3)

If this doesn't work, please post back or how about sending me PM and I'll give you my address for you to send the file.

I have had this issue many a times before. Let me know. Thanks
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
if I choose 'external data source' I do not get a wizard that guides me thru a process which then allows me to choose 'Edit in MS Query'. I get the 'Create Pivot Table' screen. ... FYI, I'm using Excel 2010.

OK. Having Excel 2010 is important. It should be mentioned up front. I'm using Excel 2003 & can't help with Excel 2010, sorry.

[FWIW, AFAIK all other versions are like I posted in the linked thread. To be sure, the steps in that other thread are,


  1. ...
  2. ...
  3. take the 'external data source' at the first step, then Get Data
  4. choose Excel files as your data source & follow wizard to end then choose option to edit in MS Query
  5. ...

That is, "Get Data" after choosing external data source. It was not 'get a wizard that guides me'.

In fact, I don't know where your source data is located, so that may impact the solution in Excel 2010 - though I don't know: I have never seen Excel 2010.
]

For the steps in Excel 2010 best I can suggest is have a bit more of a look in Excel 2010 help, or google again. I'll have a quick google at microsoft sites.

regards
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

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