Extract Number from Alphanumeric String

[dwgnome]

Looking for assistance in figuring out the best formula to extract the VOC content value from variable length alphanumeric string. The VOC content is the number just before the "g/L".

I need to extract just the number portion just before the units “g/L”. Most of the time there is a space before the number and after the number and before “g/L”. However, there are times when there may not be any spaces. In either case, I would like to extract the numbers with decimals if it has any.

See the following examples:

Excel Workbook
	D
1	SEALANT-RETAINING COMPOUND SINGLE COMPONENT ANAEROBIC, SCR40372A, *Loctite 242, 10 ML BOTTLE,146 g/L VOC
2	RESIN-EPOXY ALUMINUM FILLED 4 OUNCE PATCH KIT\ * * HYSOL 6C10 g/L VOC
3	SEALANT-THREAD LOCK *HIGH STR *MIL-S-22473 *GRADE AA *50 ML BOTTLE * * *SCR802269A\ * Loctite 089 Weld Sealant125.3 g/L VOC
4	SEALANT-RETAINING COMPOUND ANAEROBIC MIL-S-22473 * * *GRADE C 50 ML BOTTLE * *SCR802266A\ *Loctite 84 * 167.4 g/L VOC
5	RESIN-LAMINATING SYSTEM FOR REPAIR OF 250/350 DEG F CURING ADVANCED COMPOSITES ONE QUART KIT\ * EPOCAST 35A *12 g/L VOC
6	CLEANER-THRUST REVERSER 55 GAL DRUM\ *TURCO 5805 * *0 g/L VOC
7	SOLVENT-TETRAHYDROFURAN REAGENT GRADE IN 4-LITER * * JT Baker JT9450-5 * * 887 g/L VOC
8	CLEANER-AIRCRAFT PARTS * HOT TANK * UNTHICKENED *55 GALLON DRUM\ * CALA 805 *25g/L VOC
9	CLEANER\ * Fine Organics FO-585 * * *0 g/L VOC
10	SEALANT-THREAD LOCK HIGH STRENGTH MIL-S-22473 *GRADE AV, *50 ML BOTTLE, *SCR802270A, 175.2 g/L VOC
11	RESIN-EPOXY FIBERGLASS SELF EXTINGUISHING KIT BMS8-201TYII, *1 Qt Kit, *Epocast 50-A1, * *357 g/L VOC
12	CLEANER-AIRCRAFT INTERIOR UTILITY 55 GAL DRUM\ * *AEROCLEAN X-410Q * *20 g/L VOC
13	RESIN-PASTE PINHOLE FILLER FOR RADOMES 50 GRAM KIT\ * * * EPIBOND 156 A/B * * 10 g/L VOC
14	SEALANT-RETAINING COMPOUND ANAEROBIC MIL-R-46082 TY 1 *250 ML BOTTLE * *SCR18016A\ * Loctite 675 141.7g/L *VOC
15	RESIN-EPOXY CLEAR 3.35 OUNCE PATCH KIT\ * * *Hysol 0151 *Chemical Database uses UMS *ADH3002-96 * 34. g/L *VOC
Sheet1

The results I am looking for:

Excel Workbook
	H
1	146
2	10
3	125.3
4	167.4
5	12
6	0
7	887
8	25
9	0
10	175.2
11	357
12	20
13	10
14	141.7
15	34
Sheet1

Any help would be appreciated.

 

[barry houdini]

You could use this amendment to pgc's version to return a zero if "g/L" doesn't exist or if it does but with no number immediately before

=-LOOKUP(1,CHOOSE({1,2},0,LOOKUP(1,-RIGHT(TRIM(LEFT(D1,FIND("g/L",D1)-1)),{1,2,3,4,5,6}))))

 

[Richard Schollar]

Very neat Barry

Have you hired that submariner's outfit yet?

 

[Jon von der Heyden]

Have you hired that submariner's outfit yet?

Submariners tend to be very short Richard, so I can't wait to see you squeezed into yours

 

[RoryA]

Since I accepted the other invitation, I shall be in superhero garb.

 

[barry houdini]

Have you hired that submariner's outfit yet?

They're all out of submariners, some extra demand this week apparently..........

 

[dwgnome]

Jon and Barry thanks for the improved solution that pgc provided. Works great! I substituted SEARCH for FIND to allow for the few times g/l show up.

I usually do not use VBA - mainly because I just don't know VBA, but Trebor's solution is great and wouldn't mind using it. I was hoping that the UDF can be improved to get the same result.

 

[Robert Mika]

I'm trying to understand how this formula works.
Pedro, would be so kind to explain how this last bit works?
Using Evaluate Formula toll I'm stuck here:
=-LOOKUP(1,--{"4",".4","7.4","67.4","167.4"," 167.4"})

I can not figure out how the right values is being returned (in this case 164.7)
This is fourth line from dwgnome post.

Thank you in advance

 

[Jon von der Heyden]

I usually do not use VBA - mainly because I just don't know VBA, but Trebor's solution is great and wouldn't mind using it. I was hoping that the UDF can be improved to get the same result.

Not wishing to detract from Trebor's helpful input but I usually opt for a native formula solution over a UDF. In most instances they offer better efficiencies than UDF's. PGC's solution for instance is really top-notch and it would be hard to create a UDF to compete. Although I suspect Trebor's UDF solution would have beaten my initial formula method hands-down (efficiency wise).

I think you're going with the best solution available now...

 

[Matty]

Hello Robert,

I, too, have just been looking at this. My interpretation:

The RIGHT part yields an array like:

{"6","46","146",",146","E,146","LE,146"}

With the - added, we get an array like:

{-6,-46,-146,#VALUE!,#VALUE!,#VALUE!}

As the LOOKUP value is 1, it's going to pull out the -146 from the array.

Once the other - is added, this simply converts the returned value to a true number.

Have I got this right, guys?

Matty

 

[barry houdini]

I'm trying to understand how this formula works.
Pedro, would be so kind to explain how this last bit works?
Using Evaluate Formula toll I'm stuck here:
=-LOOKUP(1,--{"4",".4","7.4","67.4","167.4"," 167.4"})

I can not figure out how the right values is being returned (in this case 164.7)

Hello Robert, before RIGHT function there is a single - , i.e.

=-LOOKUP(1,-{"4",".4","7.4","67.4","167.4"," 167.4"})

so that becomes

=-LOOKUP(1,{-4,-0.4,-7.4,-67.4,-167.4,-167.4})

What you need to know about LOOKUP is that when the lookup value is greater than all the values in the array (and it should always be here because the lookup value is 1 and you'd expect all the values in the array to be negative) then the value returned is the last numeric value in the array, so by setting it up the way he has, you'll always get the full number.

If there are any letters in the 6 characters referenced then some values in the array may be errors, LOOKUP will still take the last numeric value