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.

5.3.1-WS11-564-4DB-MT <tbody> </tbody>	56
53.1-WS11-723-1DB-ABS <tbody> </tbody>	72
5.3.1-WS11-966-4DB-MT <tbody> </tbody>	96
63.1-WS11-564-3DB-MT <tbody> </tbody>	56
4.5.1-WS11-601-MT <tbody> </tbody>	60
33.1.WS11-564-6DB-UHMW <tbody> </tbody>	56
73.1-368-1DB-MT <tbody> </tbody>	36

<tbody>
</tbody>

 

[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

<tbody>
</tbody>

 

[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

<tbody>
</tbody>

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.