Grouping by partial field name

[SuperComputing]

I have been tasked with producing a report that groups data by the first part of the item number. 90% of the item numbers contain a hyphen but hardly any are the same number of characters. An example of the data pulled would be:

Item Number Qty Cost Ordered
AB1234-01SM 10 1.00 100
AB1234-01MD 20 1.10 200
AB1234-01LG 30 1.20 300
AB1234-02SM 10 1.00 100
AB1234-02MD 20 1.10 200
DCAB0004-07SM 10 1.00 100
DCAB0004-07MD 20 2.00 200
27171334 5 5.00 500

The output of the report should look like:
AB1234-01 60 1.10 600 (Sum and ave of all AB1234-01's)
AB1234-02 30 1.05 300 (Sum and ave of all AB1234-02's)
DCAB0004-07 30 1.50 300
27171334 5 5.00 500

The data after the hyphen (items that have hyphens) is a 2 digit color code, followed by a 2 letter size code. The data is to be grouped by colors

I am able to cause the item numbers to display properly with the following:
Left ({Items.ItemNumber},InStr ({Items.ItemNumber},"-")+2 )

I am not able to group, sum or average the like colors based on their new names.

Any ideas, please?

 

[lbass]

mid({Items.ItemNumber},InStr({Items.ItemNumber},"-")+1,2)

Insert a group on this formula.

-LB

 

[SuperComputing]

Great!

Under Fields, Formula Fields, I created an Item Group with:
Left ({Items.ItemNumber},InStr ({Items.ItemNumber},"-")+2 )
This gave me the name formatting that I needed.

Then a Color Group with:
mid({Items.ItemNumber},InStr({Items.ItemNumber},"-")+1,2)
This separated out my colors

Now I Insert, Summary, Qty, Sum, Location:Color Group
This does my math.

Thank you so much for your help!