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.

 

[Trebor76]

Hi dwgnome,

Based on your data, I don't think there is an excel formula (or combination of formulas) to do the job

That said, I’ve written the following User Defined Function (udf) to do extract a number from the right of a string (doesn't include negative numbers):

Function MyNumberExtract(rngCell As Range) As Currency

    Dim intChrCnt As Integer
    Dim varTempString As Variant
    
     'Searches the string from right to left, but puts the result left to right
     For intChrCnt = Len(rngCell) To 1 Step -1
        'If the current character is either numeric or a decimal point, then...
        If IsNumeric(Mid(rngCell, intChrCnt, 1)) = True Or _
           Mid(rngCell, intChrCnt, 1) = "." Then
                '...add to the 'varTempString' variable
                varTempString = Mid(rngCell, intChrCnt, 1) & varTempString
                'If the current character is not numeric or a decimal point and _
                'varTempString' variable is populated, then...
                ElseIf varTempString <> "" Then
                    '...exit the loop.
                    Exit For
                End If
    Next intChrCnt
    
    'Convert the 'varTempString' variable to a currency variable.
    MyNumberExtract = CCur(varTempString)
       
End Function

To insert the code, follow these five steps:

1. Copy (Ctrl + C) my code
2. Open the VBA editor (Alt + F11)
3. From the Insert menu click Module
4. Paste (Ctrl + V) my code from step 1 above into the blank module
5. From the File menu click Close and Return to Microsoft Excel

Then use the udf as you would any other native excel formula - i.e. =MyNumberExtract(D1)

 

[Jon von der Heyden]

Hi

Using formula (although I suspect there is a neater alternative available):

First select the range with the text and do 2 x Find&Replace.
1.
Find: [space]g/L
Replace with: g/L
2.
Find: g/L
Replace with: [space]g/L

The find&replace is so that we consistently apply a space before each occurence of g/L.

Now the following formula solution, example below:

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

Cell Formulas
Range	Formula
E1	=--MID(C1,MAX(IF(ISERROR(--MID(SUBSTITUTE(C1,".","0"),ROW(INDIRECT("1:"&FIND("g/L",C1)-2)),1)),ROW(INDIRECT("1:"&FIND("g/L",C1)-2))))+1,FIND("g/L",C1)-2-MAX(IF(ISERROR(--MID(SUBSTITUTE(C1,".","0"),ROW(INDIRECT("1:"&FIND("g/L",C1)-2)),1)),ROW(INDIRECT("1:"&FIND("g/L",C1)-2)))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[pgc01]

Hi dwgnome

Assuming numbers with no more than 6 characters, try in H1:

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

Copy down

 

[Richard Schollar]

Hi dwgnome

Assuming numbers with no more than 6 characters, try in H1:

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

Copy down

I love the -LOOKUP(1,-) form - it's just cool

 

[Jon von der Heyden]

Hi dwgnome

Assuming numbers with no more than 6 characters, try in H1:

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

Copy down

Wow - this is neat ... Don't think I've ever noticed this method before

 

[pgc01]

Thanks. This is also one of my favorites.

 

[dwgnome]

Thank you all for your great contributions.

Trebor, yours worked the best. Only thing, when there is no number before the g/L or if no presence of g/L or VOC, your UDF catches the last existing number within the string. Is there a way to have the results show zero or blank in those cases?

Jon, yours works perfectly, but only after adding the spaces before and after the g/L. However, if there is no g/L or if no number, I get #VALUE! instead of blank or zero.

pgc01, yours is the coolest. Not sure what it is doing with the minus signs. Only thing is that it produces #NA instead of zero or blank when no number or g/L is present.

Is it possible to get each of these options to show zero or blank if no VOC related number is present? I have to have a number/blank for the output will be part of additional computations.

Thanks.

 

[Jon von der Heyden]

I'm not even going to try and compete with Pedro's solutions, so I'll just nick it

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

 

[Jon von der Heyden]

Or better yet:

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