# Extract Text from String

#### legalhustler

##### Well-known Member
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

Last edited:

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Bo_Ry

##### Board Regular
Book1
AB
1AAC Classic Chicken Breast 20 90 10 70 80 0 270Classic Chicken Breast
2AAG French Fry 0 0 0 2 1 0 3French Fry
3AAH Sun Dry 20 40 20 60 30 0 170Sun Dry
4AAE Tomatoes 0 10 0 10 0 0 20Tomatoes
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=MID(LEFT(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17))-2),5,99)

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff New.xlsm
AB
1
2AAC Classic Chicken Breast 20 90 10 70 80 0 270Classic Chicken Breast
3AAG French Fry 0 0 0 2 1 0 3French Fry
4AAH Sun Dry 20 40 20 60 30 0 170Sun Dry
5AAE Tomatoes 0 10 0 10 0 0 20Tomatoes
Main
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,5,AGGREGATE(15,6,FIND(ROW(\$1:\$10)-1,A2),1)-6)

#### JimM

##### Well-known Member
Nice formula Bo_Ry could you explain how this bit works

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

#### Bo_Ry

##### Board Regular

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

##### Well-known Member
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?

Last edited:

#### legalhustler

##### Well-known Member
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

##### MrExcel MVP, Moderator
+Fluff New.xlsm
ABCDEFGHI
1
2AAC Classic Chicken Breast 20 90 10 70 80 0 270Classic Chicken Breast20901070800270
3AAG French Fry 0 0 0 2 1 0 3French Fry0002103
4AAH Sun Dry 20 40 20 60 30 0 170Sun Dry20402060300170
5AAE Tomatoes 0 10 0 10 0 0 20Tomatoes0100100020
Main
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,5,AGGREGATE(15,6,FIND(ROW(\$1:\$10)-1,A2),1)-6)
C2:I5C2=TRIM(MID(SUBSTITUTE(MID(\$A2,FIND(\$B2,\$A2)+LEN(\$B2)+1,99)," ",REPT(" ",100)),COLUMN(A2)*100-99,100))

Replies
11
Views
297
Replies
0
Views
81
Replies
2
Views
77
Replies
3
Views
77
Replies
4
Views
113