Extract only 3 consecutive numbers from string

[arembr01]

I need to search a text string, find 3 consecutive numbers in a row, then extract the first two characters. I have given an example below. The 3 consecutive numbers will always be between "-" but the occurrence of "-" will vary. The numbers also might be stored as text within the string. The text string will only be in B2. It is a part number and will be updated by the user every time. Any help is greatly appreciated.




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]5.3.1-WS11-564-4DB-MT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]53.1-WS11-723-1DB-ABS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]5.3.1-WS11-966-4DB-MT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]63.1-WS11-564-3DB-MT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]4.5.1-WS11-601-MT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]33.1.WS11-564-6DB-UHMW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]73.1-368-1DB-MT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

This array-entered formula seems to do what you are asking for...

=INT(MAX(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(A1,".","X"),"-","X"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),3),0))/10)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[arembr01]

This array-entered formula seems to do what you are asking for...

=INT(MAX(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(A1,".","X"),"-","X"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),3),0))/10)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Thank you very much for the help. However when I ran a few more tests I found a small issue. If the part number has a "/" in it then it doesn't give me the correct value.

6-TC-360-12/5DB results in 4313. It should be 36

7/3.1-WS11-484-4DB-MT results in 4328. It should be 48




[TABLE="width: 177"]
<tbody>[TR]
[TD="width: 177"] [/TD]
[/TR]
[TR]
[TD="width: 177"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you very much for the help. However when I ran a few more tests I found a small issue. If the part number has a "/" in it then it doesn't give me the correct value.

6-TC-360-12/5DB results in 4313. It should be 36

7/3.1-WS11-484-4DB-MT results in 4328. It should be 48
[TABLE="width: 177"]
<tbody>[TR]
[TD="width: 177"] [/TD]
[/TR]
[TR]
[TD="width: 177"][/TD]
[/TR]
</tbody>[/TABLE]

None of the seven examples you posted showed a slash in them so I did not know I had to work around them. Here is the fix (still an array-entered** formula)...

=INT(MAX(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","X"),".","X"),"-","X"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),3),0))/10)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[arembr01]

None of the seven examples you posted showed a slash in them so I did not know I had to work around them. Here is the fix (still an array-entered** formula)...

=INT(MAX(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","X"),".","X"),"-","X"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),3),0))/10)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

I apologize, I didn't think about that example. But once again, thank you very much for your help.