Counting pieces in a lump sentence

Hi, i have an excel here where my colleague has been working on.
I would like to count the quantity of each sports, but as the input was lumped together in an example sentence "basketball = 3 pcs" in a cell.
I am not sure exactly if vlookup or sumif can works.

One method i can think of is by dividing the cell using "text to column" into 2 columns "basket" and "3 pcs".
From there, use vlookup or sumif.

But i would like to know if there are other faster methods that can works too.

Originally Posted by alansidman

Here is a solution using Power Query/Get and Transform

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B
1	Value.1	Count
2	Balloon	10
3	Basketball	14
4	IWD	2
5	Softball	2
6	Tennis	7

Sheet: Sheet2

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

This is what i need, thank you.
Today i learn something "power query".