Text String Extraction --2 words

[Huizar]

Hello All,

Need some help. Im looking at a problem summary cell and looking to extract key words. For example if the summary has TRON Then extract TRON. If the Word has TRON or AL2, then give me TRON. If the summary has AL2 then give me AL2. I know there is an OR that goes in my formula but not sure how to use it.



[TABLE="width: 500"]
<tbody>[TR]
[TD]Problem Summary[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]TRON is OUT on NAL2/ Trend Low[/TD]
[TD]TRON[/TD]
[/TR]
[TR]
[TD]High DIff TRON[/TD]
[TD]TRON[/TD]
[/TR]
[TR]
[TD]T2 - RUNNING DNR AL2[/TD]
[TD]AL2[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[Aladin Akyurek]

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH({"AL2","TRON"},$A2),{"AL2","TRON"})

 

[Huizar]

Hey Aladin, that didn't seem to work just a bunch of #N/A. Not sure why that is occurring.

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH({"AL2","TRON"},$A2),{"AL2","TRON"})

 

[Aladin Akyurek]

Hey Aladin, that didn't seem to work just a bunch of #N/A. Not sure why that is occurring.

Shoult work as advertised...

Book1
	A	B
1	Problem Summary	Result
2	TRON is OUT on NAL2/ Trend Low	TRON
3	High DIff TRON	TRON
4	T2 - RUNNING DNR AL2	AL2
Sheet1

In B2 entered and copied down:

=LOOKUP(9.99999999999999E+307,SEARCH({"AL2","TRON"},$A2),{"AL2","TRON"})

Are you on a non-American system? If so, dot >> comma, comma >> semi-colon.

 

[Huizar]

Hey Aladain, my bad. I retried it. It worked. What is the 9.9999.. + 307 actually doing by the way?
Thanks!!!

Shoult work as advertised...

	A	B
Problem Summary	Result
TRON is OUT on NAL2/ Trend Low	TRON
High DIff TRON	TRON
T2 - RUNNING DNR AL2	AL2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet1​

In B2 entered and copied down:

=LOOKUP(9.99999999999999E+307,SEARCH({"AL2","TRON"},$A2),{"AL2","TRON"})

Are you on a non-American system? If so, dot >> comma, comma >> semi-colon.

 

[Aladin Akyurek]

Hey Aladain, my bad. I retried it. It worked. What is the 9.9999.. + 307 actually doing by the way?
Thanks!!!

You are welcome. Here are some links on the big number:

https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

Hope this helps.