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
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
This works, thanks!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

augz123

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

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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
 

augz123

New Member
Joined
Aug 25, 2015
Messages
8
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
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% KUVERT 3 KG JARLSBERG 27% KUVERT3
6
7419863 SALAMI SKIVET 8X150G ENH 419863 SALAMI SKIVET 8X150G ENH0,15
8
9431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST
10
11150GNORVEGIA 27% SKIVET 150G TINE150G NORVEGIA 27% SKIVET TINE0,15
12
13468351 SVINEBOG SALT KOKT TERNET 5KG 468351 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

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
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
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.


 

acctgdana

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

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.
 

Forum statistics

Threads
1,089,547
Messages
5,408,881
Members
403,240
Latest member
AlenKovacevic

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top