I have a variation on the problem of separating numbers from alphanumeric text that I have not managed to solve myself or find a solution for anywhere on the internet, so it’s time to seek help.
I am regularly supplied with a price list that is frequently over 1000 rows long with a cell in each row containing a product description. This description is of variable length and contains a mixture of words and numbers. The final numbers are the product quantity and are nearly always followed by the unit, such as ML, L KG or T. There is occasionally a further descriptive word following this, or sometimes a star symbol (*). The product quantity varies in size and can be a decimal value.
The bit that makes things tricky is that there is often a number within the product description itself that is often not relevant to the quantity.
The only things that are consistent is that the target number is the first number reading from right to left and there is always a space before it and the next item to the left, either text or another number. Also, there may or may not be a space between the target number and its following unit.
What I want to be able to do is extract the quantity into one column and the unit into another column. I have been focusing on formulas rather than VBA as my abilities with VBA are basic.
One formula I tried without success to modify is one this forum and was supplied by István Hirsch. The formula he supplied to a poster seeking to extract decimal values was :
=LOOKUP(10^8,--MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),{1,2,3,4,5,6,7,8}))
Unfortunately this only extracts the first numbers from the left whereas I want the numbers from the right.
I am using Windows 8.1 and Excel 2010.
A data sample and what I would like to extract is shown below:
<tbody>
</tbody>
I am regularly supplied with a price list that is frequently over 1000 rows long with a cell in each row containing a product description. This description is of variable length and contains a mixture of words and numbers. The final numbers are the product quantity and are nearly always followed by the unit, such as ML, L KG or T. There is occasionally a further descriptive word following this, or sometimes a star symbol (*). The product quantity varies in size and can be a decimal value.
The bit that makes things tricky is that there is often a number within the product description itself that is often not relevant to the quantity.
The only things that are consistent is that the target number is the first number reading from right to left and there is always a space before it and the next item to the left, either text or another number. Also, there may or may not be a space between the target number and its following unit.
What I want to be able to do is extract the quantity into one column and the unit into another column. I have been focusing on formulas rather than VBA as my abilities with VBA are basic.
One formula I tried without success to modify is one this forum and was supplied by István Hirsch. The formula he supplied to a poster seeking to extract decimal values was :
=LOOKUP(10^8,--MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),{1,2,3,4,5,6,7,8}))
Unfortunately this only extracts the first numbers from the left whereas I want the numbers from the right.
I am using Windows 8.1 and Excel 2010.
A data sample and what I would like to extract is shown below:
| DESCRIPTION | SIZE | UNIT |
| PRODUCTA 100 500G | 500 | G |
| PRODUCTB 100 500 G | 500 | G |
| PRODUCTC 500 G | 500 | G |
| PRODUCTC 500 G BOTTLE | 500 | G |
| PRODUCTD 250 WP 2.5KG | 2.5 | KG |
| PRODUCTD 250 WP 2.5 KG | 2.5 | KG |
| PRODUCTE 400DF 3 KG * | 3 | KG |
| PRODUCTG 11-12-19 25 KG | 25 | KG |
| PRODUCTH PRODUCTHH 10KG | 10 | KG |
| PRODUCTH PRODUCTHH 10 KG | 10 | KG |
| PRODUCTH PRODUCTHH PRODUCTHHH 10 KG | 10 | KG |
| PRODUCTJ 45.55KG | 45.55 | KG |
| PRODUCTJ 45.55 KG * | 45.55 | KG |
| PRODUCTK 5 5L | 5 | L |
| PRODUCTK 5 5 L * | 5 | L |
| PRODUCTL-15 5 L | 5 | L |
| PRODUCTM 1000 500ML | 500 | ML |
| PRODUCTM 1000 500 ML | 500 | ML |
| PRODUCTN + PRODUCTO 1T | 1 | T |
| PRODUCTN + PRODUCTO 1.5T * | 1.5 | T |
| PRODUCTN + PRODUCTO 1.5 T * | 1.5 | T |
| PRODUCTN + PRODUCTO 1.5 T BAG | 1.5 | T |
<tbody>
</tbody>
