How to pull part of a pivot table field

[Rhino_Dance]

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.

 

[cyrilbrd]

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.

 

[Rhino_Dance]

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]

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]

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

 

[Fazza]

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

 

[Fazza]

maybe this one is OK? http://www.mrexcel.com/forum/excel-questions/503179-pivot-tables-count-function.html#post2483137

otherwise please search. cheers

 

[Rhino_Dance]

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]

Hi Rhino !

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

=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]

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