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.

 

[Jon von der Heyden]

I'm enjoying following this, so thanks from me too

 

[firozmoideen]

this is simply amazing Great Job!!!

thanks

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)