Extract Text Found in a Cell That Appears After Text That is Found in a Separate Cell

[danimill14]

Hello,

More confusing coming up with a title than anything.

So I am looking to extract where each item is shipping from, which is found in Column B, using the product details found in column D. Unfortunately, if the order has multiple items, they are all listed in one cell. I can pull the first item, but never any beyond that.

Tried a variety of REPLACE, SUBSTITUTE, LEFT, RIGHT and MID formulas but can't quite figure it out.

Any suggestions would be greatly appreciated.

Thanks!

[alansidman]

Is this what you are looking to do? Your explanation was a bit vague on what your expectation was.

Excel 2016 (Windows) 32 bit

	A	B	C
1	Shipped From	Notes	Order
2	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
3	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
4	DS	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
5	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
6	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
7	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
8	DS	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
9	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
10	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
11	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
12	DS	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
13	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
14	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
15	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
16	DS	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
17	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675

Sheet: Sheet1

Achieved with Power Query/Get and Transform

See attached.

[danimill14]

Thanks for the response Alan.

What I am trying to do is lookup the item found in column D within the text string in column B, and return where it says that item was shipped from.

For example in row 4, I would like to lookup "Premium Poster Calendar....", found in cell D4, within cell B4 and return the "shipping from" value. For this example, it would return Toronto.

Thank you,

Dan

[alansidman]

Here is new Mcode

Please Login or Register  to view this content.

Data now looks like the following

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Shipped From	Notes	Notes - Copy	Order
2	DS	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
3	DS	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
4	DS	Toronto	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
5	DS	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
6	DS	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
7	DS	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
8	DS	Toronto	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
9	DS	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
10	DS	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
11	DS	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
12	DS	Toronto	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
13	DS	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675
14	DS	DS	FLAT SHOELACES 54" / RED - 54 shipping from DS; Qty 1.0	215675
15	DS	DS	SCARF / GREY PLAID - ONE SIZE shipping from DS; Qty 1.0	215675
16	DS	Toronto	Premium Poster Calendar Issue 42 - The No Doubt Issue - One Size shipping from Toronto; Qty 1.0	215675
17	DS	DS	CALCULATOR WATCH / GOLD - ONE SIZE shipping from DS; Qty 1.0	215675

Sheet: Sheet1

[danimill14]

How would the query look if I only wanted the solutions to appear in column A without creating the additional rows? Can that be done?

This is exactly what I'm looking for though in terms of result.

[alansidman]

I don't understand what you want. Please mock up a solution based upon the information you have already provided.