Extract Text from String

[legalhustler]

Hi,

In the text string below, I would like a formula to extract the following: Classic Chicken Breast, French Fry, Sun Dry, Tomatoes.

The string always starts with three characters followed by a space. No Power Query or Dynamic Arrays solutions, just need to use regular functions due to Excel version limitations.

AAC Classic Chicken Breast 20 90 10 70 80 0 270
AAG French Fry 0 0 0 2 1 0 3
AAH Sun Dry 20 40 20 60 30 0 170
AAE Tomatoes 0 10 0 10 0 0 20

Additionally, I would like to extract each of the numbers to the right before the space. So 20, 90, 10, 70, 80, 0, 270 for first one and second 0, 0, 0, 2, 1, 0, 3

 

[Bo_Ry]

Please try

Book1
	A	B
1	AAC Classic Chicken Breast 20 90 10 70 80 0 270	Classic Chicken Breast
2	AAG French Fry 0 0 0 2 1 0 3	French Fry
3	AAH Sun Dry 20 40 20 60 30 0 170	Sun Dry
4	AAE Tomatoes 0 10 0 10 0 0 20	Tomatoes
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=MID(LEFT(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17))-2),5,99)

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B
1
2	AAC Classic Chicken Breast 20 90 10 70 80 0 270	Classic Chicken Breast
3	AAG French Fry 0 0 0 2 1 0 3	French Fry
4	AAH Sun Dry 20 40 20 60 30 0 170	Sun Dry
5	AAE Tomatoes 0 10 0 10 0 0 20	Tomatoes
Main

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,5,AGGREGATE(15,6,FIND(ROW($1:$10)-1,A2),1)-6)

 

[JimM]

Nice formula Bo_Ry could you explain how this bit works

(FIND({0,1,2,3,4}+{0;5},A1&1/17)

 

[Bo_Ry]

Shorter one
=MID(A1,5,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17))-6)

{0,1,2,3,4}+{0;5} return number from 0-9
&1/17 also give 0-9 from 0.05882352941176 that help to prevent #Value when Find cannot find 0-9 in A1

 

[legalhustler]

Shorter one
=MID(A1,5,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17))-6)

{0,1,2,3,4}+{0;5} return number from 0-9
&1/17 also give 0-9 from 0.05882352941176 that help to prevent #Value when Find cannot find 0-9 in A1

Interesting solution.

How does {0,1,2,3,4}+{0;5} give you 0-9? How is it adding the two array constants?

Why does only 1/17 work and not 1/16, 1/18 etc?

 

[legalhustler]

Also, need formula to extract the numbers to the right in separate columns. So from the text string in OP in separates columns 20, 90, 10, 70, 80, 0, 270 for first one and second 0, 0, 0, 2, 1, 0, 3

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2	AAC Classic Chicken Breast 20 90 10 70 80 0 270	Classic Chicken Breast	20	90	10	70	80	0	270
3	AAG French Fry 0 0 0 2 1 0 3	French Fry	0	0	0	2	1	0	3
4	AAH Sun Dry 20 40 20 60 30 0 170	Sun Dry	20	40	20	60	30	0	170
5	AAE Tomatoes 0 10 0 10 0 0 20	Tomatoes	0	10	0	10	0	0	20
Main

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,5,AGGREGATE(15,6,FIND(ROW($1:$10)-1,A2),1)-6)
C2:I5	C2	=TRIM(MID(SUBSTITUTE(MID($A2,FIND($B2,$A2)+LEN($B2)+1,99)," ",REPT(" ",100)),COLUMN(A2)*100-99,100))