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>

 

[eddymoney88]

HM... Maybe I'm not doing something right. Does Mac Excel have anything to do with it? Because when I enter the UDF (Tools-Macro-VB Editor-Add Macro) and then type in the =getnumber function, it comes back "#VALUE"

 

[pgc01]

Does Mac Excel have anything to do with it?

Hi

That's something you should always say when you post.

Some options

1 -
I don't have the mac but I think hat the mac does not support the creation of the regex object the way it's done in the udf.
From what I've understood in recent versions of AppleScript you can use Unix shell commands that support some degree of regex's.
As I said I don't work with the mac, but for ex., if you have access to awk you're more than good to go.

I'm sure that there are a lot of ways to implement regex's in the mac, since it's an old, stable technology, but I cannot help you there.

2 -
You implement a udf that does not use regex's.
Scan the text for a number followed by g or kg.
That's just looping through the characters.


3 -
Another way would be to use the formula solution. Eric's formula will work for the integer values.
I guess you'll have to tweak it to accept decimals, since that's something you did not post before and the formula will not deal with it correctly.

 

[Eric W]

Here's a version that will handle decimals for the kg part. Still maximum of 3 characters though, although that can be raised by increasing the {1,2,3} arrays.

	A	B	C
1	???????????????????3kg?????????	3
2	???? ???????? ??????500g?? ????	500
3	????????????????????????500g*2?	500
4	??/????/????3KG/????/????/????????	3
5	???? ??????????????3kg ????????	3
6	??/?????/????15KG/???????????????	15
7	???? ??????3kg????????????500g*6???	3	x
8	???????1.5kg???????????????????	1.5
9	???? ????????????500g??????????	500
10	?? ?????? ?????????????200g ???	200
11	??????????????????3kg??????????	3
12	??????????????????1.5kg????????	1.5
13	???????????????????????????3kg6?	3

<tbody>
</tbody>

Sheet1​

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

<tbody>
</tbody>

Line 7 has both 3kg and 500g in it, but the kg will take precedence. The {"0g","1g"...} array can be shortened to just "0g" if you only expect "round" numbers for the g part. This formula works on all the samples I've tested, but it still is highly dependent on the data being in the form it expects.

 

[pgc01]

The {"0g","1g"...} array can be shortened ...

Hi Eric

Another way to write the array could be:

{0,1,2,3,4,5,6,7,8,9}&"g"

 

[Eric W]

Hi Eric

Another way to write the array could be:

{0,1,2,3,4,5,6,7,8,9}&"g"

Clever. Thanks for the tip.