Separate number from text

[sean1541]

Hi I want to separate the number from the text
using 2016 excel

Potatoes Prepared 162kg
Potatoes Prepared	162kg

Book1
	A	B	C	D	E
1
2	Potatoes Prepared 162kg
3	Carrots Diced 80kg
4	Bananas - box 92kg
5	Spanish Onions - bag 10kg
6	Lettuce 35 Heads
7	Stir Fry Veg Mix - bag 25kg
8	Mixed Peppers - box 1kg
9	Mushrooms - carton 11kg
10	Leeks - bag 5kg
11	Tomatoes 20kg
12
13	Turnips - Diced 80kg
14	Potatoes Prepared 10kg
15	Garlic Strings 1kg
16	Stir Fry Veg Mix - bag 25kg
17	Cauliflower 3 Heads
18	Broccoli 6kg
19	Coleslaw Mix 15kg
20
21
Inventory

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"})
in
    #"Split Column by Character Transition"

Book1
	A	B	C	D
3	Column1		Column1.1	Column1.2
4	Potatoes Prepared 162kg		Potatoes Prepared	162kg
5	Carrots Diced 80kg		Carrots Diced	80kg
6	Bananas - box 92kg		Bananas - box	92kg
7	Spanish Onions - bag 10kg		Spanish Onions - bag	10kg
8	Lettuce 35 Heads		Lettuce	35 Heads
9	Stir Fry Veg Mix - bag 25kg		Stir Fry Veg Mix - bag	25kg
10	Mixed Peppers - box 1kg		Mixed Peppers - box	1kg
11	Mushrooms - carton 11kg		Mushrooms - carton	11kg
12	Leeks - bag 5kg		Leeks - bag	5kg
13	Tomatoes 20kg		Tomatoes	20kg
14
15	Turnips - Diced 80kg		Turnips - Diced	80kg
16	Potatoes Prepared 10kg		Potatoes Prepared	10kg
17	Garlic Strings 1kg		Garlic Strings	1kg
18	Stir Fry Veg Mix - bag 25kg		Stir Fry Veg Mix - bag	25kg
19	Cauliflower 3 Heads		Cauliflower	3 Heads
20	Broccoli 6kg		Broccoli	6kg
21	Coleslaw Mix 15kg		Coleslaw Mix	15kg
Sheet1

 

[mohadin]

Hi
A VBA!!

Sub test()
Dim a, m
Dim i&
a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+.+"
        For i = 1 To UBound(a)
        If (Trim(a(i, 1))) <> "" Then
        Set m = .Execute(a(i, 1))
        a(i, 2) = m(0)
        a(i, 3) = Trim(Replace(a(i, 1), m(0), ""))
        End If
      Next
    End With
Cells(2, 1).Resize(UBound(a), UBound(a, 2)) = a
End Sub

 

[mohadin]

Or

Sub test()
Dim a, m
Dim i&
a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+.+"
        For i = 1 To UBound(a)
        If (Trim(a(i, 1))) <> "" Then
        Set m = .Execute(a(i, 1))
        a(i, 3) = m(0)
        a(i, 2) = Trim(Replace(a(i, 1), m(0), ""))
        End If
      Next
    End With
Cells(2, 1).Resize(UBound(a), UBound(a, 2)) = a
End Sub

 

[Peter_SSs]

With Formulas?

23 03 04.xlsm
	A	B	C
1
2	Potatoes Prepared 162kg	Potatoes Prepared	162kg
3	Carrots Diced 80kg	Carrots Diced	80kg
4	Bananas - box 92kg	Bananas - box	92kg
5	Spanish Onions - bag 10kg	Spanish Onions - bag	10kg
6	Lettuce 35 Heads	Lettuce	35 Heads
7	Stir Fry Veg Mix - bag 25kg	Stir Fry Veg Mix - bag	25kg
8	Mixed Peppers - box 1kg	Mixed Peppers - box	1kg
9	Mushrooms - carton 11kg	Mushrooms - carton	11kg
10	Leeks - bag 5kg	Leeks - bag	5kg
11	Tomatoes 20kg	Tomatoes	20kg
12
13	Turnips - Diced 80kg	Turnips - Diced	80kg
14	Potatoes Prepared 10kg	Potatoes Prepared	10kg
15	Garlic Strings 1kg	Garlic Strings	1kg
16	Stir Fry Veg Mix - bag 25kg	Stir Fry Veg Mix - bag	25kg
17	Cauliflower 3 Heads	Cauliflower	3 Heads
18	Broccoli 6kg	Broccoli	6kg
19	Coleslaw Mix 15kg	Coleslaw Mix	15kg
Split

Cell Formulas
Range	Formula
B2:B19	B2	=TRIM(LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1))
C2:C19	C2	=TRIM(REPLACE(A2,1,LEN(B2),""))

 

[sean1541]

Thanks a million this really helps me.
Regards
Sean.

 

[sean1541]

With Formulas?

23 03 04.xlsm
	A	B	C
1
2	Potatoes Prepared 162kg	Potatoes Prepared	162kg
3	Carrots Diced 80kg	Carrots Diced	80kg
4	Bananas - box 92kg	Bananas - box	92kg
5	Spanish Onions - bag 10kg	Spanish Onions - bag	10kg
6	Lettuce 35 Heads	Lettuce	35 Heads
7	Stir Fry Veg Mix - bag 25kg	Stir Fry Veg Mix - bag	25kg
8	Mixed Peppers - box 1kg	Mixed Peppers - box	1kg
9	Mushrooms - carton 11kg	Mushrooms - carton	11kg
10	Leeks - bag 5kg	Leeks - bag	5kg
11	Tomatoes 20kg	Tomatoes	20kg
12
13	Turnips - Diced 80kg	Turnips - Diced	80kg
14	Potatoes Prepared 10kg	Potatoes Prepared	10kg
15	Garlic Strings 1kg	Garlic Strings	1kg
16	Stir Fry Veg Mix - bag 25kg	Stir Fry Veg Mix - bag	25kg
17	Cauliflower 3 Heads	Cauliflower	3 Heads
18	Broccoli 6kg	Broccoli	6kg
19	Coleslaw Mix 15kg	Coleslaw Mix	15kg
Split

Cell Formulas
Range	Formula
B2:B19	B2	=TRIM(LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1))
C2:C19	C2	=TRIM(REPLACE(A2,1,LEN(B2),""))

Hi is there a way to extract bag, box etc into a separate column.

 

[JEC]

This should do

Sub jec()
 Dim ar, i As Long
 ar = Range("A2").CurrentRegion.Resize(, 3)
 With CreateObject("VBScript.RegExp")
   For i = 1 To UBound(ar)
     .Global = True
     .Pattern = "(?:.*?)(\d+)(.+)(?=$)"
      ar(i, 2) = .Replace(ar(i, 1), "$1")
      ar(i, 3) = Trim(.Replace(ar(i, 1), "$2"))
   Next
   Range("A2").CurrentRegion.Resize(, 3) = ar
 End With
End Sub

 

[sean1541]

This should do

Sub jec()
 Dim ar, i As Long
 ar = Range("A2").CurrentRegion.Resize(, 3)
 With CreateObject("VBScript.RegExp")
   For i = 1 To UBound(ar)
     .Global = True
     .Pattern = "(?:.*?)(\d+)(.+)(?=$)"
      ar(i, 2) = .Replace(ar(i, 1), "$1")
      ar(i, 3) = Trim(.Replace(ar(i, 1), "$2"))
   Next
   Range("A2").CurrentRegion.Resize(, 3) = ar
 End With
End Sub

With CreateObject("VBScript.RegExp")

this is coming as error

 

[sean1541]

With CreateObject("VBScript.RegExp")

this is coming as error

Sorry it is working my fault
Thanks for your help.