Translate or Convert Text in Excel to Consolidated Numbers

Registration date
Friday December 4, 2015
Last seen
December 4, 2015

I am quite new to Visual Basic for Applications, but I am trying to figure out if a specific part of what I am trying is possible/how to go about attempting to create a macro to achieve my goals.

I am attempting to automate our process of manually entering in inventory from a storefront (our point-of-sale system unfortunately lacks a few features) to get data that we can analyze based on days, weeks, months, etc. Essentially, we want to see the trends of specific items and decide whether to increase or decrease production.

Our current system will allow us to download daily data of the day's sales, but the program itself will not show us any "trends".

What I am trying to do, is to create a macro that will read the data, find what is relevant, consolidate the data, and then copy that information into a "master" data sheet where we then can use all of the individual "daily" data to make analyses.

Current issue:
We have multiple quantities of a specific item existing as descriptions in text with quantities of those "items". I want to consolidate all of this information into one row in our "Master" data.


Whole Pie x 5
Half Pie x 2
Quarter Pie x 4
6 Pack of Rolls x 3
1 Roll x 20

Post Macro (stored in Master excel sheet):
Pie = 7 [1 x 5 + 0.5 x 2 + 0.25 x 4]
Rolls = 38 [6 x 3 + 20 x 1]

Is it possible to do this? My assumption was some sort of "Key" would have to be provided to understand that "Whole X" or "6 pack of Y" are treated as numbers under that "Item" so that they then can be copied and pasted as shown in my "Post Macro" example.

Any assistance or guidance that I could receive would be wonderful and very appreciated!