# 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
May be,

=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!

### Excel Facts

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

#### augz123

##### New Member
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
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
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``````
 A B C D 1 2,5KG STORFE HOYRYGG STRIMLET GILDE 2,5KG STORFE HOYRYGG STRIMLET GILDE 2,5 2 3 0,7L BLABARSAFT 0,7l LERUM 0,7L BLABARSAFT 0,7l LERUM 0,7 4 5 3 KG JARLSBERG 27% KUVERT 3 KG JARLSBERG 27% KUVERT 3 6 7 419863 SALAMI SKIVET 8X150G ENH 419863 SALAMI SKIVET 8X150G ENH 0,15 8 9 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 431087 LAM LAPSKAUSKJOTT SALT TERNET FRYST 10 11 150G NORVEGIA 27% SKIVET 150G TINE 150G NORVEGIA 27% SKIVET TINE 0,15 12 13 468351 SVINEBOG SALT KOKT TERNET 5KG 468351 SVINEBOG SALT KOKT TERNET 5KG 5 14 15 24G BRINGEBARSYLTETOY KUVERT 24G LERUM 24G BRINGEBARSYLTETOY KUVERT LERUM 0,024 16 200STK SMOR KUVERT 12G 200STK SMOR KUVERT 12G 0,012 17 18 5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG 5KG ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG 5 19 20 0,9L HUSHOLDNINGSSAFT U/SUKKER 0,9L LERUM 0,9L HUSHOLDNINGSSAFT U/SUKKER LERUM 0,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
In this line:

ROKT KJOTTPOLSE UTEN SKINN 2X2,6KG

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

#### acctgdana

##### New Member
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
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
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.

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