Extract a certain word in a cell

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

Board Regular
Joined
Aug 13, 2015
Messages
176
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
1OL_123 - Please Keep 522ro
OL_123
2OTHER: OL_4362465 - Discard6
OL_4362465
3Other: 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,328
Office Version
  1. 2010
Platform
  1. Windows
Give this normally-entered formula a try...
Code:
[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]
 
Last edited:

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,308
Messages
5,527,930
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top