Extricate 1-3 Digit Numbers Before Certain Keywords

[eddymoney88]

I have an worksheet where I need to find all the weights (in kilograms and grams), but I only want to find the numbers (as I will easily be able to tell if they're KG or G just by the value it returns). If it's kilos, then the value I'm looking for could have 1 or 2 digits, and if it's in grams then it will always be 3 digits.

I've used: =IFERROR(MID(F2,SEARCH("kg",F2)-2,2),MID(F2,SEARCH("G",F2)-3,3)) but it returns a specific number of characters, whereas I want to just isolate the numbers.


Here's an example

PureDogFood3KG	3
CatFood500GBag	500
PuppyFood15KG	15

<tbody>
</tbody>

 

[FormR]

Hi, welcome to the forum.

Here is one possibility you could try:

Excel 2013
	A	B
1	PureDogFood3KG	3
2	CatFood500GBag	500
3	PuppyFood15KG	15
4	Sometext500gsometext	500
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(SUBSTITUTE(SUBSTITUTE(UPPER(MID(A1,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1),1),255)),"G",REPT(" ",13)),"K",REPT(" ",13)),13)+0

 

[Sektor]

UDF:

Function GetNumber(cell)
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+": GetNumber = .Execute(cell.Value)(0)
    End With
End Function

 

[eddymoney88]

Hi, welcome to the forum.

Here is one possibility you could try:

Excel 2013

	A	B
1	PureDogFood3KG	3
2	CatFood500GBag	500
3	PuppyFood15KG	15
4	Sometext500gsometext	500

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =LEFT(SUBSTITUTE(SUBSTITUTE(UPPER(MID(A1,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1),1),255)),"G",REPT(" ",13)),"K",REPT(" ",13)),13)+0 <tbody> </tbody>

<tbody>
</tbody>

Thanks!!!!

 

[eddymoney88]

I'm running into another problem after using this formula... I see that there are some strings of text that include other numbers.

For instance:

1-3yearoldPuppyFood5KG
5+yearoldCat500gBag

Etc.

 

[oldbrewer]

PureDogFood3KG	3
CatFood500GBag	500
PuppyFood15KG	15
PureDogFood3KG				3			row 13
CatFood500GBag				500
PuppyFood15KG				15
				this macro strips out the numbers
				For j = 13 To 15
				ll = Len(Cells(j, 1))
				For k = 1 To 100
				If k > ll Then GoTo 97
				temp1 = Mid(Cells(j, 1), k, 1)
				temp2 = Asc(temp1)
				If temp2 < 48 Then GoTo 95
				If temp2 > 57 Then GoTo 95
				90 Cells(j, 5) = Cells(j, 5) & temp1
				95 Next k
				97 Next j
				100 End Sub

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

 

[pgc01]

Hi

A small tweak to Sektor's solution, testing that the number is followed by "g" or "kg"

Function GetNumber(s As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(?=(g|kg))"
        .IgnoreCase = True
        With .Execute(s)
            If .Count > 0 Then GetNumber = .Item(0) Else GetNumber = "N/A"
        End With
    End With
End Function

Ex.:

In B1: =GetNumber(A1)
Copy down


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Level1DogFood3KG</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">CatFood500GBag</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">500</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1-3yearoldPuppyFood5KG</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Sometext500sometext</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">5+yearoldCat300gBag</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">300</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1.xlsm]Sheet4</td></tr></table>

 

[Eric W]

Here's a formula solution, which relies on the size of the numbers being in the ranges you mentioned:

	A	B
1	Level1DogFood3KG	3
2	CatFood500GBag	500
3	1-3yearoldPuppyFood5KG	5
4	Sometext500sometext	no match
5	5+yearoldCat300gBag	300

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =IFERROR(AGGREGATE(14,3,MID(A1,SEARCH("kg",A1)-{2,1},{2,1})+0,1),IFERROR(MID(A1,AGGREGATE(14,3,SEARCH({"0g","1g","2g","3g","4g","5g","6g","7g","8g","9g"},A1)-2,1),3)+0,"no match")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[eddymoney88]

Now, I have to do this sort of calculation for multiple countries. One of them is China, and when I use the UDF it doesn't seem to work. Does it have to do with language/characters? Below is an example of what the spreadsheet looks like, and I still have the same task of finding the numerical digits before "kg" or "g"

波奇网雀巢狗粮冠能小型犬挑食美毛成犬粮3kg贵宾泰迪粮全国包邮

原装进口 美国冠能赛级狗粮 美冠赛级犬粮500g分装 整包优惠

冠能幼犬狗粮通用型犬粮贵宾泰迪萨摩金毛哈士奇主粮500g*2包

冠能/幼犬狗粮/成长配方3KG/泰迪金毛/贵宾博美/萨摩耶哈士奇边牧

冠能狗粮 宠优冠能挑食美毛小型犬成犬粮3kg 泰迪贵宾狗犬主粮

冠能/大型犬幼犬/成长狗粮15KG/金毛犬拉多阿拉斯加萨摩边牧德牧

冠能狗粮 成犬良好消化3kg贵宾泰迪金毛成犬通用犬粮500g*6袋包邮

冠能狗粮幼犬粮1.5kg鸡肉米饭幼犬奶糕粮泰迪金毛通用狗粮包邮

姜露宠物 宠优冠能狗粮宠物幼犬狗粮500g泰迪小型犬大型犬金毛

冠能 泰迪比熊贵宾 小型犬成犬挑食美毛天然狗粮200g 试用装

冠能狗粮泰迪狗粮小型犬挑嘴挑食及美毛3kg贵宾比熊通用美毛狗粮

宠尤冠能幼犬狗粮鸡肉米饭配方优启成长1.5kg成犬狗粮泰迪金毛

冠能狗粮小型犬挑食及美毛配方成犬博美泰迪贵宾贵妇主狗粮3kg6斤

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

 

[pgc01]

Hi

The udf worked for me in all cases except the ones with 1.5 because I did not expect decimal numbers.
In those 2 cases it would return 5, since that is the sequence of digits before the Kg

To also account for the numbers with decimals change the pattern to:

        .Pattern = "\d+(\.\d+)?(?=(g|kg))"

With these pattern all worked for me.