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]

separating Bag Box etc

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"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.2", type text}})
in
    #"Changed Type1"

Potatoes Prepared		162kg
Carrots Diced		80kg
Bananas	box	92kg
Spanish Onions	bag	10kg
Lettuce		35 Heads
Stir Fry Veg Mix	bag	25kg
Mixed Peppers	box	1kg
Mushrooms	carton	11kg
Leeks	bag	5kg
Tomatoes		20kg
Turnips	Diced	80kg
Potatoes Prepared		10kg
Garlic Strings		1kg
Stir Fry Veg Mix	bag	25kg
Cauliflower		3 Heads
Broccoli		6kg
Coleslaw Mix		15kg

 

[Peter_SSs]

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

With formulas, is this what you are after?

23 03 04.xlsm
	A	B	C	D
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 (2)

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

With CreateObject("VBScript.RegExp")

Sorry it is working my fault

Does that mean that you are not using MacOS per your profile, or perhaps that you have Windows as well?

 

[sean1541]

With formulas, is this what you are after?

23 03 04.xlsm
	A	B	C	D
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 (2)

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

Does that mean that you are not using MacOS per your profile, or perhaps that you have Windows as well?

Thanks a million for your help this works great
Can I expand it down the row ?

 

[Peter_SSs]

Thanks a million for your help this works great

You're welcome. thanks for the follow-up.

Can I expand it down the row ?

Give it a go & see!!

 

[sean1541]

You're welcome. thanks for the follow-up.


Give it a go & see!!

Thanks again works great.

 

[Peter_SSs]

Thanks again works great.

Good news.

BTW, this has a simpler formula for column B and still seems to do the job.

23 03 04.xlsm
	A	B	C	D
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 (3)

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

 

[alansidman]

Can I expand it down the row ?

Add new data to your source document and then click on Refresh All. PQ will update automatically

 

[sean1541]

Add new data to your source document and then click on Refresh All. PQ will update automatically

Thank you for your help.