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.


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]2,5KG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]STORFE HØYRYGG STRIMLET GILDE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]0,7L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]BLÅBÆRSAFT 0,7L LERUM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"] 3 KG JARLSBERG 27% KUVERT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]419863 SALAMI SKIVET 8X150G ENH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]431087 LAM LAPSKAUSKJØTT SALT TERNET FRYST[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]150G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]NORVEGIA 27% SKIVET 150G TINE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]468351 SVINEBOG SALT KOKT TERNET 5KG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]24G[/TD]
[TD="align: center"]BRINGEBÆRSYLTETØY KUVERT 24G LERUM[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]200STK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]SMØR KUVERT 12G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]5KG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]RØKT KJØTTPØLSE UTEN SKINN 2X2,5KG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="align: center"]0,9L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]HUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
"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.
 
Upvote 0
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.
 
Upvote 0
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
2,5KGSTORFE HOYRYGG STRIMLET GILDE2,5KG STORFE HOYRYGG STRIMLET GILDE 2,5KG
** **
0,7LBLABARSAFT 0,7l LERUM0,7L BLABARSAFT 0,7l LERUM 0,7L
** **
*3 KG JARLSBERG 27% KUVERT 3 KG JARLSBERG 27% KUVERT 3 KG
** **
*419863 SALAMI SKIVET 8X150G ENH 419863 SALAMI SKIVET 8X150G ENH 0,15KG
** **
*431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST *
** **
150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET *TINE 0,15KG
** **
*468351 SVINEBOG SALT KOKT TERNET 5KG 468351 SVINEBOG SALT KOKT TERNET 5KG 5KG
** **
24GBRINGEBARSYLTETOY KUVERT 24G LERUM24G BRINGEBARSYLTETOY KUVERT *LERUM 0,024KG
200STKSMOR KUVERT 12G200STK SMOR KUVERT 12G 0,012KG
** **
5KGROKT 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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

</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.
 
Upvote 0
......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
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top