Extract Text from String

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)
 
Upvote 0
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)
 
Upvote 0
Nice formula Bo_Ry could you explain how this bit works

(FIND({0,1,2,3,4}+{0;5},A1&1/17)
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top