Extract Text from String

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,091
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:

Some videos you may like

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
Joined
Oct 27, 2018
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Please try
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
Joined
Jun 12, 2014
Messages
48,138
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 11, 2003
Messages
682
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
Joined
Oct 27, 2018
Messages
119
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 5, 2014
Messages
1,091
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
Joined
Jun 5, 2014
Messages
1,091
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
Joined
Jun 12, 2014
Messages
48,138
Office Version
  1. 365
Platform
  1. Windows
How about
+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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,638
Members
410,696
Latest member
JTrehan
Top