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.


[TABLE="width: 590"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Product ID[/TD]
[TD]Product Name[/TD]
[TD]Starting char. for no. [/TD]
[TD]Pack size from text[/TD]
[/TR]
[TR]
[TD]5959302[/TD]
[TD]Chocolate 180g[/TD]
[TD]11[/TD]
[TD]180g[/TD]
[/TR]
[TR]
[TD]4197003[/TD]
[TD]Delice aux 4 poivres 75g[/TD]
[TD]12[/TD]
[TD]4 poivres 75g[/TD]
[/TR]
[TR]
[TD]4326029[/TD]
[TD]Crisps 6 Portions 120g[/TD]
[TD]8[/TD]
[TD]6 Portions 120g[/TD]
[/TR]
[TR]
[TD]3456411[/TD]
[TD]Fondue 1% Trüfflen 600g[/TD]
[TD]8[/TD]
[TD]1% Trüfflen 600g[/TD]
[/TR]
[TR]
[TD]5614167[/TD]
[TD]Bio Yog 120g FE[/TD]
[TD]9[/TD]
[TD]120g FE[/TD]
[/TR]
[TR]
[TD]6034195[/TD]
[TD]Fondue 100% Vacherin 450g[/TD]
[TD]8[/TD]
[TD]100% Vacherin 450g[/TD]
[/TR]
[TR]
[TD]5770331[/TD]
[TD]Camembert -55% Fett 125g[/TD]
[TD]12[/TD]
[TD]55% Fett 125g[/TD]
[/TR]
[TR]
[TD]5843635[/TD]
[TD]Babybel 18 St. 396g[/TD]
[TD]9[/TD]
[TD]18 St. 396g[/TD]
[/TR]
</tbody>[/TABLE]

 

[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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Product ID​

[/td][td]

Product Name​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

5959302​

[/td][td]

Chocolate 180g​

[/td][td]

180g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

4197003​

[/td][td]

Delice aux 4 poivres 75g​

[/td][td]

75g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

4326029​

[/td][td]

Crisps 6 Portions 120g​

[/td][td]

120g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3456411​

[/td][td]

Fondue 1% Trüfflen 600g​

[/td][td]

600g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5614167​

[/td][td]

Bio Yog 120g FE​

[/td][td]

120g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

6034195​

[/td][td]

Fondue 100% Vacherin 450g​

[/td][td]

450g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

5770331​

[/td][td]

Camembert -55% Fett 125g​

[/td][td]

125g​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

5843635​

[/td][td]

Babybel 18 St. 396g​

[/td][td]

396g​

[/td][/tr]
[/table]

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))

[TABLE="width: 390"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product ID
[/TD]
[TD]Product Name
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5959302
[/TD]
[TD]Chocolate 180g
[/TD]
[TD]180g[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4197003
[/TD]
[TD]Delice aux 4 poivres 75g
[/TD]
[TD]75g
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4326029
[/TD]
[TD]Crisps 6 Portions 120g
[/TD]
[TD]120g
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3456411
[/TD]
[TD]Fondue 1% Trüfflen 600g
[/TD]
[TD]600g
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5614167
[/TD]
[TD]Bio Yog 120g FE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6034195
[/TD]
[TD]Fondue 100% Vacherin 450g
[/TD]
[TD]450g
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5770331
[/TD]
[TD]Camembert -55% Fett 125g
[/TD]
[TD]125g
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5843635
[/TD]
[TD]Babybel 18 St. 396g
[/TD]
[TD]396g
[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

What about listing all possible units with some examples?