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

A

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]
Click to expand...
 
Last edited:
Sort by date Sort by votes
bosco_yip said:
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
Click to expand...

This works, thanks!
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
alansidman said:
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
Click to expand...

This works like a charm, thanks. I had to move the "* 0.0001" part down two rows and take away a zero, but otherwise perfect
 
Upvote 0
Here is the version which does not have the units. Following the method I suggested you should get a result similar to mine (see below).

Code: 
Function FindUnit(s As String) As String
    With CreateObject("VBScript.Regexp")
        .ignorecase = True
        .Global = True
        .Pattern = "([\d,]+[ ]*(kg|g|l))[ ]|.+?"
        If .test(s) Then FindUnit = .Replace(s, "$1")
        p = InStr(1, FindUnit, "KG")
        If p > 0 Then FindUnit = Mid(FindUnit, 1, p - 1)
        x = InStr(1, FindUnit, "G")
       If x > 0 Then FindUnit = (Mid(FindUnit, 1, Len(FindUnit) - 1) / 1000)
        Z = InStr(1, FindUnit, "L")
        If Z > 0 Then FindUnit = Mid(FindUnit, 1, Z - 1)
    End With
End Function
Excel Workbook
ABCD
12,5KGSTORFE HOYRYGG STRIMLET GILDE2,5KG STORFE HOYRYGG STRIMLET GILDE2,5
2
30,7LBLABARSAFT 0,7l LERUM0,7L BLABARSAFT 0,7l LERUM0,7
4
53 KG JARLSBERG 27% KUVERT3 KG JARLSBERG 27% KUVERT3
6
7419863 SALAMI SKIVET 8X150G ENH419863 SALAMI SKIVET 8X150G ENH0,15
8
9431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST
10
11150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET TINE0,15
12
13468351 SVINEBOG SALT KOKT TERNET 5KG468351 SVINEBOG SALT KOKT TERNET 5KG5
14
1524GBRINGEBARSYLTETOY KUVERT 24G LERUM24G BRINGEBARSYLTETOY KUVERT LERUM0,024
16200STKSMOR KUVERT 12G200STK SMOR KUVERT 12G0,012
17
185KGROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5
19
200,9LHUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM0,9L HUSHOLDNINGSSAFT U/SUKKER LERUM0,9
Sheet
 
Upvote 0
István Hirsch said:
Here is the version which does not have the units. Following the method I suggested you should get a result similar to mine (see below).

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

ABCD
2,5KGSTORFE HOYRYGG STRIMLET GILDE2,5KG STORFE HOYRYGG STRIMLET GILDE
0,7LBLABARSAFT 0,7l LERUM0,7L BLABARSAFT 0,7l LERUM
3 KG JARLSBERG 27% KUVERT 3 KG JARLSBERG 27% KUVERT
419863 SALAMI SKIVET 8X150G ENH 419863 SALAMI SKIVET 8X150G ENH
431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST
150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET TINE
468351 SVINEBOG SALT KOKT TERNET 5KG 468351 SVINEBOG SALT KOKT TERNET 5KG
24GBRINGEBARSYLTETOY KUVERT 24G LERUM24G BRINGEBARSYLTETOY KUVERT LERUM
200STKSMOR KUVERT 12G200STK SMOR KUVERT 12G
5KGROKT KJOTTPOLSE UTEN SKINN 2X2,6KG5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG
0,9LHUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM0,9L HUSHOLDNINGSSAFT U/SUKKER LERUM

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2,5[/TD]

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

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

[TD="align: right"]0,7[/TD]

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

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

[TD="align: right"]3[/TD]

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

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

[TD="align: right"]0,15[/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="align: right"]0,15[/TD]

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

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

[TD="align: right"]5[/TD]

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

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

[TD="align: right"]0,024[/TD]

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

[TD="align: right"]0,012[/TD]

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

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

[TD="align: right"]5[/TD]

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

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

[TD="align: right"]0,9[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

This worked perfectly, thanks!

I see that I need to extract the the numbers before the X's (the standalone 2 below) as well into a separate colum, but I think I'll be able to do it with the what you and the others in the thread have provided.

ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG
 
Upvote 0
In this line:

ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG

I changed your original 2,5 to 2,6 just to test something at an earlier version of my code.


 
Upvote 0
You can do this with a formula for column A, rather than VBA.

I would add two columns to store the data.

Formula for quantity: =IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)),"N/A")="N/A",IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("L",A2))+1)),"N/A")="N/A",IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("G",A2))+1)),"N/A")="N/A","Missing",LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("G",A2))+1))),LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("L",A2))+1))),LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)))

Formula for unit of measure: =IF(IFERROR(MID(A2,FIND("KG",A2),LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)),"N/A")="N/A",IF(IFERROR(MID(A2,FIND("L",A2),LEN(A2)-(LEN(A2)-(FIND("L",A2))+1)),"N/A")="N/A",IF(IFERROR(MID(A2,FIND("G",A2),LEN(A2)-(LEN(A2)-(FIND("G",A2))+1)),"N/A")="N/A","Missing",MID(A2,FIND("G",A2),LEN(A2)-(LEN(A2)-(FIND("G",A2))+1))),MID(A2,FIND("L",A2),LEN(A2)-(LEN(A2)-(FIND("L",A2))+1))),MID(A2,FIND("KG",A2),LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)))

Using the formulas will work better if you have other people using the file and don't want them having VBA issues, or if internal security prevents enabling VBA.

I'm not certain how to parse the column B text string using this formula though. That might require the VBA scripting option.
 
Upvote 0
If you need a formula, try this. Enter it into C1 and copy down:

=IFERROR(-LOOKUP(1;-MID(A1&" "&B1&" ";FIND("KG ";A1&" "&B1&" ")-{1;2;3;4;5;6;7;8};{1;2;3;4;5;6;7;8}));"")&IFERROR(-LOOKUP(1;-MID(A1&" "&B1&" ";FIND("L ";A1&" "&B1&" "&"L ")-{1;2;3;4;5;6;7;8};{1;2;3;4;5;6;7;8}));"")&IFERROR(-LOOKUP(1;-MID(A1&" "&B1&" ";FIND("G ";A1&" "&B1&" "&"G ")-{1;2;3;4;5;6;7;8};{1;2;3;4;5;6;7;8}))/1000;"")
Excel Workbook
ABC
12,5KGSTORFE HOYRYGG STRIMLET GILDE2,5
2
30,7LBLABARSAFT 0,7l LERUM0,7
4sdfsdf ddfde 4KG jaheree4
53 KG JARLSBERG 27% KUVERT3
6
7419863 SALAMI SKIVET 8X150G ENH0,15
8
9431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST
10
11150GNORVEGIA 27% SKIVET 150G TINE0,15
12
13468351 SVINEBOG SALT KOKT TERNET 5KG5
14
1524GBRINGEBARSYLTETOY KUVERT 24G LERUM0,024
16200STKSMOR KUVERT 12G0,012
17
185KGROKT KJOTTPOLSE UTEN SKINN 2X2,5KG5
19
200,9LHUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM0,9
Sheet
 
Upvote 0
Note: the formula in post # will give false negative if the data to extract is preceded by a word ending in the unit, for example:


JARLSBERG 27% KUVERT 150G


It won't extract 150.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,885
Messages
6,181,582
Members
453,055
Latest member
cope7895

Share this page

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