MID Function | Search multiple words

romeo123

New Member
Joined
May 14, 2018
Messages
1
I need to extract a word between 2 different strings. The first strings starts in any of the following words- LTD, LP, FD or LTD. And it ends before the word "TWO". I'm thinking of using mid function but it can only be used for one argument.

Example:

I need to extract the following : ASWU, ASDU, ASWU from this data

[TABLE="width: 145"]
<tbody>[TR]
[TD]LTD ASWU TWO[/TD]
[/TR]
[TR]
[TD]LP ASDU TWO[/TD]
[/TR]
[TR]
[TD]FD ASWU TWO[/TD]
[/TR]
[TR]
[TD]LTD ASWU TWO

I hope you could help me guys. thank you
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
this works for your sample data, btw you have two LTD in your matched data!


Excel 2013/2016
AB
1LTD ASWU TWOASWU
2LP ASDU TWOASDU
3FD ASWU TWOASWU
4LTDX ASWU TWOX
5LTD ASWU TWOASWU
6LTD ASWU ONEX
Sheet1
Cell Formulas
RangeFormula
B1{=IF(AND(ISNUMBER(LOOKUP(99,SEARCH({"LTD ","LP ","FD "},A1))),RIGHT(A1,3)="TWO"),SUBSTITUTE(MID(SUBSTITUTE(" " & A1&REPT(" ",6)," ",REPT(" ",255)),2*255,255)," ",""),"X")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Welcome to the MrExcel board!

I suspect that your sample data is not very representative of your actual data and you may need to give a more varied set of data and expected results.
Otherwise one of these fairly simple formulas would produce those results for your sample data.

Excel Workbook
ABC
1LTD ASWU TWOASWUASWU
2LP ASDU TWOASDUASDU
3FD ASWU TWOASWUASWU
4LTD ASWU TWOASWUASWU
Extract Word
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,834
Members
452,674
Latest member
psion2600

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