A Pivot table issue

Y

Yakimoto

Hi everybody!

Is it possible to create a pivot table, but to use only a part of a field as
a row filed, not an entire field.
I have in the column 'Parts' thge following data
Parts Numbers
---------
Vehicle-Doors 1
Vehicle-Wheels 2

and I woulf like to create a pivot, but somehow to have Left(part,6), so I
will have grouping by Vehicle only.
I can manage this adding additoonal column to the sheet, but I think there
should be a way using some formula or so.

Thanks in advance

../Yakimo
 
K

Kevin McCartney

Hi,
You'll need to create a new column and place formula
like you have written, but use '=left(A1,Find("-",A1)-1)'.
You can then use this new column in your pivot table. You
need to give the column a name as well, like Part Group or
something, your choice.

regards
KM
 
Y

Yakimoto

Thanks, Kevin.
With a new column it is clear how to do.
I was just wondering if there is a way without adding additional columns.
Does anyone now something?

Cheers,
Yakimoto
 
M

Mike Waldron

Hello Yakimoto

The simplest solution is to create a new field in your
data source. Insert a column to the right of the vechile-
number column. Then use the Text to Columns from the
Excel DATA drop down menu. With that command you can
parse the numbers into the new field. Once that is
finished you can form the pivot table you want.

This breakes the original field into two fields. If you
need to keep the original field intact then you could use
the Mid() function to copy only the desired portion of the
original field into the new field.

Mike
 
Y

Yakimoto

Thanks Mike,
Is there any other solution without adding a new column? Is it posible to do
that using pivot table formulas (calculated fields, calculated items or
whatever) ?
 
T

Tom Ogilvy

Insert a new column A and put in a formula like


=left(B2,6)

Put in an appropriate label/header in A1. Drag fill A2 down the column.

Now create you pivot table.
 
Top