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]

 

[Rick Rothstein]

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]

Is that the exact structure of all your data... text, an X, a space, a number, a space, a slash, a space, the number you want, a space, the word MIN or MINS followed by more text? If not, show us a representative sampling of what your cells can contain and tell us the desired value for each.

 

[Gfletch63]

Is that the exact structure of all your data... text, an X, a space, a number, a space, a slash, a space, the number you want, a space, the word MIN or MINS followed by more text? If not, show us a representative sampling of what your cells can contain and tell us the desired value for each.

Here's a few sample cells, which all occur in the same column:

[TABLE="width: 197"]
<tbody>[TR]
[TD]PTG X 4 / 60 MINS UNLD
[TABLE="width: 383"]
<tbody>[TR]
[TD]85 MINS UNLD ( 1 HR 25 MINS)
[TABLE="width: 383"]
<tbody>[TR]
[TD]50 MINS UNLD
[TABLE="width: 383"]
<tbody>[TR]
[TD]DEL APPT / 29 MINS UNLD

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
So sometimes the desired information occurs at the beginning (85). sometimes it's in the middle (60, as in the first example). The other text varies, but the number always occurs just before the MIN or MINS text. In english my lgic is to find the position in the string in which the three letters MIN occur and then return the previous 4 characters (to cover those instances where both 2 or 3 digits occur); however, that's a problem when the number is 2 digits and occurs at the beginning of the string.

Thanks.

 

[Rick Rothstein]

The word MIN or MINS is always to the right of the number, and the number is always either 2 or 3 digits.

If the number is never a single digit, then would the word MIN (no "S") ever immediately follow your number?

 

[Gfletch63]

If the number is never a single digit, then would the word MIN (no "S") ever immediately follow your number?

The words MINS (including the S) is almost always used; I just wanted to cover the possibility that only MIN (without the S) might occur. These entries are manually entered notes, so it's possible that someone might fail to use the S.

The number is always 2 or 3 digits.

Thanks.

 

[jtakw]

Hi,

This should work for you:

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

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

 

[Gfletch63]

Hi,

This should work for you:

	A	B	C
PTG X 4 / 60 MINS UNLD
85 MINS UNLD ( 1 HR 25 MINS)
185 MINS UNLD ( 3 HR 5 MINS)
50 MINS UNLD
99MIN ABCD
DEL APPT / 29 MINS UNLD

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

[TD="align: right"][/TD]
[TD="align: right"]60[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]85[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]185[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]29[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=RIGHT(TRIM(LEFT(A1,SEARCH("MIN",A1)-1)),3)+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

It does, terrific. Thanks, jtakw, and also thanks to Rick Rothstein as well. This board and the people who respond to these questions are invaluable.

 

[Rick Rothstein]

It does, terrific. Thanks, jtakw, and also thanks to Rick Rothstein as well. This board and the people who respond to these questions are invaluable.

Just double checking as it could affect jtakw's formula... keeping in mind that we know nothing about your data, is it at all possible that the letters MIN might appear before the number you want? I am thinking of words like "MINI", "EXAMINE", and such.

 

[Gfletch63]

Just double checking as it could affect jtakw's formula... keeping in mind that we know nothing about your data, is it at all possible that the letters MIN might appear before the number you want? I am thinking of words like "MINI", "EXAMINE", and such.

No. fortunately the manual entry of these notes has proven to be very consistent.

 

[jtakw]

You're welcome, welcome to the forum, glad it worked for you.