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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]OL_123 - Please Keep
[/TD]
[TD]OL_123
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]OTHER: OL_4362465 - Discard
[/TD]
[TD]OL_4362465
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Other: OL_010a
[/TD]
[TD]OL_010
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]OL_555a - Trust
[/TD]
[TD]OL_555
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]OL_123 - Please Keep 522ro
[/TD]
[TD]OL_123
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]OTHER: OL_4362465 - Discard6
[/TD]
[TD]OL_4362465
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Other: OL_010a - Other - 46
[/TD]
[TD]OL_010
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]OL_555a - Trust
[/TD]
[TD]OL_555
[/TD]
[/TR]
</tbody>[/TABLE]

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!