Extract numbers from strings in two different collumns if a certain set of letters follows numbers

[Augz123]

So, I have two columns where both might possibly contain the numbers I want to extract to a third column in the same row. The numbers I am looking to extract will allways be followed by a "G", an "L" or a "KG". As G denotes grams it would be great if the numbers extracted before a G would come out with the comma moved three spaces forward, i.e. so that 24G would become 0,0024 in the third column.

In the first column, A, there will only be the unit of measurement preceded by the number, but in many cases column A will be empty (see table for data). In those cases I'd like to check the longer text/number string in column B, same row, for the numbers. In column B the number might be preceeded by another number and an X like this: 8X150G. In those cases I would like only the 150 to be extracted.

The commas are not a problem as my country for some reason has choses a different standard than the English one.

Having tried on my own to no avail I realise that this requires quite a bit of work so all help is much appreciated, even only for the first column. A formula would be preferable but I am of course open to VBA as well.

I am using Excel 2013 on Windows 8. The data is 40 000+ rows long if that's applicable to the solution Apologies in advance for the table aestethics

A ------------------- B
2,5KG ---- STORFE HØYRYGG STRIMLET GILDE

0,7L ---- BLÅBÆRSAFT 0,7L LERUM

3 KG ---- JARLSBERG 27% KUVERT

------------------- 419863 SALAMI SKIVET 8X150G ENH

------------------- 431087 LAM LAPSKAUSKJØTT SALT TERNET FRYST

150G ---- NORVEGIA 27% SKIVET 150G TINE

------------------- 468351 VINEBOG SALT KOKT TERNET 5KG

24G ---- BRINGEBÆRSYLTETØY KUVERT 24G LERUM

200STK ---- SMØR KUVERT 12G

5KG ---- RØKT KJØTTPØLSE UTEN SKINN 2X2,5KG

0,9L ---- HUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM

 

[Nebu]

Hi:

Th following code can do 90% of formatting I guess.

Sub ExtractSBData()

Dim regEx
Dim i As Long
Dim pattern As String
Set regEx = CreateObject("VBScript.RegExp")
regEx.IgnoreCase = True
regEx.Global = True
regEx.pattern = "([$ABCDEFHIJNOPQRSTUVWXYZ/&Ø.]|[$0-9]*[X]|[$0-9]*[^\s]*[$%]|[$0-9]*[\s])"

    For i = 2 To ActiveSheet.UsedRange.Rows.Count + 1
    On Error Resume Next
        If (regEx.Test(Cells(i, 2).Value)) And Cells(i, 1).Value = vbNullString Then
            Cells(i, 3).Value = regEx.Replace(Trim(Cells(i, 2).Value), "")
        Else
            Cells(i, 3).Value = Cells(i, 1).Value
        End If
    Next i
regEx.IgnoreCase = True
regEx.Global = True
regEx.pattern = "(^[$A-Z]*[$A-Z])"

    For i = 2 To ActiveSheet.UsedRange.Rows.Count + 1
    On Error Resume Next
        If (regEx.Test(Cells(i, 3).Value)) And Cells(i, 1).Value = vbNullString Then
            Cells(i, 3).Value = regEx.Replace(Trim(Cells(i, 3).Value), "")
        Else
            Cells(i, 3).Value = Cells(i, 1).Value
        End If
    Next i

End Sub

Add the reference Microsoft VBScript Regular Expression , before running the macro.

Find the attached.

Thanks