Pulling numbers from a string

[Mr2017]

Hi

I'd like to pull numbers from text for some lines.

The example below has just 4 columns - the product ID and description, then two columns with the formulas I've used.

The first formula (in column C) shows the starting point of the first number in the text in column B.

The second formula (in column D) shows the actual number.

However, there are two issues:
i) the second formula sometimes pulls in letters after the numbers eg 180g instead of 180 (without the "g") and
ii) where there are two sets of numbers in the text description, the formula returns both sets of numbers eg 4 poivres 75g when it should just be 75g.

Does anyone know a way around this?

Thanks in advance.

Product ID	Product Name	Starting char. for no.	Pack size from text
5959302	Chocolate 180g	11	180g
4197003	Delice aux 4 poivres 75g	12	4 poivres 75g
4326029	Crisps 6 Portions 120g	8	6 Portions 120g
3456411	Fondue 1% Trüfflen 600g	8	1% Trüfflen 600g
5614167	Bio Yog 120g FE	9	120g FE
6034195	Fondue 100% Vacherin 450g	8	100% Vacherin 450g
5770331	Camembert -55% Fett 125g	12	55% Fett 125g
5843635	Babybel 18 St. 396g	9	18 St. 396g

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Fennek]

Hello,

is it right to search always for gramms, i.e. a number followed the letter "g" w/o a blank?

In this case RegEx with

.Pattern = "\d+g"

would help.

regards

 

[Mr2017]

Hi Fennek

Thanks for the prompt response.

It varies - grams, CL, ML, ST, etc.

And other times it could be 4x20g.....

Is there a way around that?

Thanks

 

[Fennek]

Hi,

as a "proof of concept":

Sub Test()
'condition: no blank between numbers and unit
Range("A1") = "asdh 4 efa 12g"
Tx = Cells(1, 1)
For k = Len(Tx) To 1 Step -1
    If Mid(Tx, k, 1) Like "#" Then
        B = InStrRev(Tx, " ", k)
        Debug.Print B, Mid(Tx, B)
        Exit For
    End If
Next k
End Sub

regards

 

[Mr2017]

Thanks again for the prompt response.

Do you know if there's a way to do it without VBA / using formulas only?

 

[Marcelo Branco]

Maybe - works for the examples you showed

	A​	B​	C​
1​	Product ID​	Product Name​	Result​
2​	5959302​	Chocolate 180g​	180g​
3​	4197003​	Delice aux 4 poivres 75g​	75g​
4​	4326029​	Crisps 6 Portions 120g​	120g​
5​	3456411​	Fondue 1% Trüfflen 600g​	600g​
6​	5614167​	Bio Yog 120g FE​	120g​
7​	6034195​	Fondue 100% Vacherin 450g​	450g​
8​	5770331​	Camembert -55% Fett 125g​	125g​
9​	5843635​	Babybel 18 St. 396g​	396g​

Formula in C2 copied down
=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",200)),LOOKUP(9.99E+307,SEARCH({0,1,2,3,4,5,6,7,8,9}&"g",SUBSTITUTE(B2," ",REPT(" ",200))))-100,200))

M.

 

[Mr2017]

Ok, thanks Marco.

 

[admiral100]

Hi,

If in most cases the number is in the last word in the product name, you can try the following formula:

C2 =TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),100))

	A	B	C
1	Product ID	Product Name	Result
2	5959302	Chocolate 180g	180g
3	4197003	Delice aux 4 poivres 75g	75g
4	4326029	Crisps 6 Portions 120g	120g
5	3456411	Fondue 1% Trüfflen 600g	600g
6	5614167	Bio Yog 120g FE
7	6034195	Fondue 100% Vacherin 450g	450g
8	5770331	Camembert -55% Fett 125g	125g
9	5843635	Babybel 18 St. 396g	396g

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[István Hirsch]

What about listing all possible units with some examples?