Extract specific number from alphanumeric string

[Gfletch63]

Here is a sample string from a cell in my spreadsheet:

[TABLE="width: 197"]
<tbody>[TR]
[TD]PTG X 4 / 60 MINS UNLD

I need to grab the 60 out of that string. The word MIN or MINS is always to the right of the number, and the number is always either 2 or 3 digits.

Sounds like it should be simple, but...not for me. [/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

This seems enough:

=MID(" "&A1,SEARCH("MIN"," "&A1)-4,4)+0

 

[Gfletch63]

This seems enough:

=MID(" "&A1,SEARCH("MIN"," "&A1)-4,4)+0

Thanks, that works, too. I'm not sure yet, but it may be more flexible for use with some other things. Gonna work with both of these, depends a lot on my ability to understand the logic, of course.

 

[jtakw]

Hi, I initially started by using the MID function also, but encountered Errors if incase the User did not enter a Space before MIN:

Book1
	A	B	C	D
1	PTG X 4 / 60MINS UNLD		60	#VALUE!
2	85 MINS UNLD ( 1 HR 25 MINS)		85	85
3	185MINS UNLD ( 3 HR 5 MINS)		185	185
4	50 MINS UNLD		50	50
5	99MIN ABCD		99	#VALUE!
6	DEL APPT / 29 MINS UNLD		29	29
Sheet7

Cell Formulas
Range	Formula
C1	=RIGHT(TRIM(LEFT(A1,SEARCH("MIN",A1)-1)),3)+0
D1	=MID(" "&A1,SEARCH("MIN"," "&A1)-4,4)+0

 

[Rick Rothstein]

Here is a modification to István's formula that appears to work...

=MID(" "&TRIM(SUBSTITUTE(A1,"MIN"," MIN")),SEARCH("MIN"," "&TRIM(SUBSTITUTE(A1,"MIN"," MIN")))-4,4)+0

 

[István Hirsch]

@jtakw Yes, your solution is a bit more "bullet proof".