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

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

-----Original Message-----
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


.

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

"Kevin McCartney" wrote in message
...
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

-----Original Message-----
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


.

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
-----Original Message-----
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


.

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) ?

"Mike Waldron" wrote in message
...
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
-----Original Message-----
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


.

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.

--
regards,
Tom Ogilvy

"Yakimoto" wrote in message
...
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