MID Function | Search multiple words

[romeo123]

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

LTD ASWU TWO

LP ASDU TWO

FD ASWU TWO

LTD ASWU TWO I hope you could help me guys. thank you

<tbody>
</tbody><colgroup><col></colgroup>

 

[AlanY]

this works for your sample data, btw you have two LTD in your matched data!

Excel 2013/2016
	A	B
1	LTD ASWU TWO	ASWU
2	LP ASDU TWO	ASDU
3	FD ASWU TWO	ASWU
4	LTDX ASWU TWO	X
5	LTD ASWU TWO	ASWU
6	LTD ASWU ONE	X
Sheet1

Cell Formulas
Range	Formula
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.

 

[JackDanIce]

Another suggestion to try (regular formula):

=IF(ISERR(FIND(" ",A1,1+FIND(" ",A1))),"",MID(A1,FIND(" ",A1)+1,FIND(" ",A1,1+FIND(" ",A1))-FIND(" ",A1)-1))

Source: https://www.extendoffice.com/documents/excel/1764-excel-extract-middle-name.html

 

[Peter_SSs]

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
	A	B	C
1	LTD ASWU TWO	ASWU	ASWU
2	LP ASDU TWO	ASDU	ASDU
3	FD ASWU TWO	ASWU	ASWU
4	LTD ASWU TWO	ASWU	ASWU
Extract Word