Extract numbers from strings in two different collumns if a certain set of letters follows. Choice of column based on IF statement

augz123

New Member
Joined
Aug 25, 2015
Messages
8
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.


AB
2,5KG

<tbody>
</tbody>
STORFE HØYRYGG STRIMLET GILDE

<tbody>
</tbody>
0,7L

<tbody>
</tbody>
BLÅBÆRSAFT 0,7L LERUM

<tbody>
</tbody>
3 KG JARLSBERG 27% KUVERT

<tbody>
</tbody>
419863 SALAMI SKIVET 8X150G ENH

<tbody>
</tbody>
431087 LAM LAPSKAUSKJØTT SALT TERNET FRYST

<tbody>
</tbody>
150G

<tbody>
</tbody>
NORVEGIA 27% SKIVET 150G TINE

<tbody>
</tbody>
468351 SVINEBOG SALT KOKT TERNET 5KG

<tbody>
</tbody>
24GBRINGEBÆRSYLTETØY KUVERT 24G LERUM
200STK

<tbody>
</tbody>
SMØR KUVERT 12G

<tbody>
</tbody>
5KG

<tbody>
</tbody>
RØKT KJØTTPØLSE UTEN SKINN 2X2,5KG

<tbody>
</tbody>
0,9L

<tbody>
</tbody>
HUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,202
Office Version
2019
Platform
Windows
Here's the first part

Code:
Sub ams()
    Dim lr As Long, i As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    For i = 1 To lr
    If Range("A" & i) Like "*KG" Then
    Range("C" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 2) * 0.0001
    ElseIf Range("A" & i) Like "*G" Then
    Range("C" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 1)
    ElseIf Range("A" & i) Like "*L" Then
    Range("C" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 1)
    End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub
 

augz123

New Member
Joined
Aug 25, 2015
Messages
8
Excuse my ignorance, but I can't get it to run as it returns the following message:

"The macros in this project are disabled"

I have tried activiating all Macros in the trust center
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
This approach may be a bit more complicated than necessary, but works for the samples given. It uses a helper column, C.

Enter into C1 and copy down:

=A1&" "&SUBSTITUTE(B1;A1;"")&" "

Copy this code into an empty module:

Code:
Function FindUnit(s As String) As String
   
    With CreateObject("VBScript.Regexp")
        .Global = True
        .Pattern = "([\d,]+[ ]*(kg|g|l))[ ]|.+?"
        If .test(s) Then FindUnit = .Replace(s, "$1")
        p = InStr(1, FindUnit, "G")
        If p > 0 Then
            FindUnit = Mid(FindUnit, 1, p)
            s = InStr(1, FindUnit, "K")
            If s = 0 Then FindUnit = (Mid(FindUnit, 1, Len(FindUnit) - 1)) / 1000 & "KG"
        End If
        Z = InStr(1, FindUnit, "L")
        If Z > 0 Then FindUnit = Mid(FindUnit, 1, Z)
     End With
End Function
Then enter into D1 and copy down:

D1=FindUnit(C1)
Excel Workbook
ABCD
12,5KGSTORFE HOYRYGG STRIMLET GILDE2,5KG STORFE HOYRYGG STRIMLET GILDE2,5KG
2
30,7LBLABARSAFT 0,7l LERUM0,7L BLABARSAFT 0,7l LERUM0,7L
4
53 KG JARLSBERG 27% KUVERT3 KG JARLSBERG 27% KUVERT3 KG
6
7419863 SALAMI SKIVET 8X150G ENH419863 SALAMI SKIVET 8X150G ENH0,15KG
8
9431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST
10
11150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET TINE0,15KG
12
13468351 SVINEBOG SALT KOKT TERNET 5KG468351 SVINEBOG SALT KOKT TERNET 5KG5KG
14
1524GBRINGEBARSYLTETOY KUVERT 24G LERUM24G BRINGEBARSYLTETOY KUVERT LERUM0,024KG
16200STKSMOR KUVERT 12G200STK SMOR KUVERT 12G0,012KG
17
185KGROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5KG
Sheet
 

augz123

New Member
Joined
Aug 25, 2015
Messages
8
Thanks for the answer!

The formula part works, but neing very new to VBA I for some reason can't the code to run. Unlike the other pieces of code I have tried before it doesn't seem to register the code and the option to run is shown as faded. I'll try to look into this more myself, but any help is of course much appreciated!

Just looking at the code and the ouput it it seems that column D is a string. What I would like is for this to be actual numbers as I will be doing calculations with it.
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
You are on the spreadsheet. Now hold down Alt key and press F11 key - you get to the Microsoft Visual Basic editor. Click on Insert, and click on Module from the drop down menu. Copy/paste the code in my post there and close Microsoft Visual Basic editor. Now you are on the spreadsheet again. Enter into D1:


=FindUnit(c1) and copy down. You should get 25kg in D1 and so on according to the table I attached to my post. Let me know if it does not work this way.


If you do not want the KG and L units, how will you know which number refers to which?


When you save the workbook, choose Excel Macro-Enabled Workbook otherwise the code will not be saved.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,202
Office Version
2019
Platform
Windows
"The macros in this project are disabled"
In addition to the trust center, you will have to change the xlsx extension to xlsm or the macros won't run.
 

augz123

New Member
Joined
Aug 25, 2015
Messages
8
If you do not want the KG and L units, how will you know which number refers to which?

.
The worksheet is an aggregated list with goods from different suppliers. They've already been sorted into categories and the job I am doing now is using this list to create another list in another worksheet where competing goods of aproximately same size within categories and subcategories are grouped together. Among other things this list tells me average prices per kilo/liter total gross spend and total kilos purchased within a subcategeory.

As I have to do a manual evaluation of the rows before copying to the next sheet I will be able to see the unit of measurement before copying. What I am doing right now is automating the process of calculating the average prices per kilo/liter total gross spend and total kilos purchased before copying. The only thing I am missing is not having to evaluate the weight/liters before taking the total.

I have however been successful in extracting KG and L numbers from column A with this formula:

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG","")/1,A2))

I've tried also extracting the grams with this:

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG",""),"G","")/1000,A2))

But I can't seem to find a way to isolate the "G"-part so that it is the only thing divided by 1000.
 

augz123

New Member
Joined
Aug 25, 2015
Messages
8
This approach may be a bit more complicated than necessary, but works for the samples given. It uses a helper column, C.

Enter into C1 and copy down:

=A1&" "&SUBSTITUTE(B1;A1;"")&" "

Copy this code into an empty module:

Code:
Function FindUnit(s As String) As String
   
    With CreateObject("VBScript.Regexp")
        .Global = True
        .Pattern = "([\d,]+[ ]*(kg|g|l))[ ]|.+?"
        If .test(s) Then FindUnit = .Replace(s, "$1")
        p = InStr(1, FindUnit, "G")
        If p > 0 Then
            FindUnit = Mid(FindUnit, 1, p)
            s = InStr(1, FindUnit, "K")
            If s = 0 Then FindUnit = (Mid(FindUnit, 1, Len(FindUnit) - 1)) / 1000 & "KG"
        End If
        Z = InStr(1, FindUnit, "L")
        If Z > 0 Then FindUnit = Mid(FindUnit, 1, Z)
     End With
End Function
Then enter into D1 and copy down:

D1=FindUnit(C1)

*ABCD
12,5KGSTORFE HOYRYGG STRIMLET GILDE2,5KG STORFE HOYRYGG STRIMLET GILDE 2,5KG
2** **
30,7LBLABARSAFT 0,7l LERUM0,7L BLABARSAFT 0,7l LERUM 0,7L
4** **
5*3 KG JARLSBERG 27% KUVERT 3 KG JARLSBERG 27% KUVERT 3 KG
6** **
7*419863 SALAMI SKIVET 8X150G ENH 419863 SALAMI SKIVET 8X150G ENH 0,15KG
8** **
9*431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST *
10** **
11150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET *TINE 0,15KG
12** **
13*468351 SVINEBOG SALT KOKT TERNET 5KG 468351 SVINEBOG SALT KOKT TERNET 5KG 5KG
14** **
1524GBRINGEBARSYLTETOY KUVERT 24G LERUM24G BRINGEBARSYLTETOY KUVERT *LERUM 0,024KG
16200STKSMOR KUVERT 12G200STK SMOR KUVERT 12G 0,012KG
17** **
185KGROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG 5KG

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:196px;"><col style="width:208px;"><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Please disregard the previous post. I got the code to run and the formula appears to be active. At first Excel seemed a bit more "static" without cells being highlighted when clicked and the function returned what it was supposed to. But after shutting down Excel and starting up again the cells are once again highlighted with a green rectangle and the formula doesn't work eventhough it's "active" and appear when I type it in.

If I could get this to work this will in combination with alansidman's work solve my problem.

I'll look a bit more into it. Any input is appreaciated yet again.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
......I have however been successful in extracting KG and L numbers from column A with this formula:

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG","")/1,A2))

I've tried also extracting the grams with this:

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG",""),"G","")/1000,A2))

But I can't seem to find a way to isolate the "G"-part so that it is the only thing divided by 1000.
May be,

Change your formula

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG",""),"G","")/1000,A2))

into this :

=IF(ISBLANK(l4332);B2,IFERROR(SUBSTITUTE(A2,"G","")/1000,SUBSTITUTE(SUBSTITUTE(A2,"L",""),"KG","")))

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,090,298
Messages
5,413,641
Members
403,496
Latest member
chamshop

This Week's Hot Topics

Top