Extract a certain word in a cell

[kparadise]

Hello,

I have a column of cells that can be anywhere from 0 characters - 2,000 characters. I am trying to extract from this cell a code; if the code exists - extract it, if it doesn't, tell me it doesn't. The code always starts with "OL_" and is followed by numbers (i.e OL_123, OL_4562, OL_90). This code can be anywhere within the cell. Also, it is possible that there is a letter value which is after the last number (i.e. OL_56234a, OL_90H). I do not want those letter values.

I hope the below helps to see what I need. Column B are the results I need.

	A	B
1	OL_123 - Please Keep	OL_123
2	OTHER: OL_4362465 - Discard	OL_4362465
3	Other: OL_010a	OL_010
4	OL_555a - Trust	OL_555

<tbody>
</tbody>

 

[njimack]

Confirm with ctrl+shift+enter:
=MID(A1,FIND("OL_",A1),MAX(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1)))))-FIND("OL_",A1)+1)

 

[kparadise]

It looks like this is picking up more characters then I want. How would this formula change if there can be more numbers within the cell.

	A	B
1	OL_123 - Please Keep 522ro	OL_123
2	OTHER: OL_4362465 - Discard6	OL_4362465
3	Other: OL_010a - Other - 46	OL_010
4	OL_555a - Trust	OL_555

<tbody>
</tbody>

For instance, I added additional numbers to [Column A] so you can try to see what I am talking about. I only want the numbers after the OL_

 

[Rick Rothstein]

Give this normally-entered formula a try...

[table="width: 500"]
[tr]
	[td]=IFERROR("OL_"&MID(-LOOKUP(0,-LEFT(MID(SUBSTITUTE(A1,"_","_1"),3+FIND("OL_",A1),LEN(A1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))),2,LEN(A1)),"** No Code **")[/td]
[/tr]
[/table]

 

[njimack]

Getting closer - the below works in some instances with numbers later in the string, but still needs tweaking...
=MID(A1,FIND("OL_",A1),MIN(FIND(" ",A1,FIND("OL_",A1)),MAX(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1))))))-FIND("OL_",A1)+1)

 

[kparadise]

Rick, thank you that worked perfectly!