extracting numbers only from columns

[aka_krakur]

I have a column in a worksheet that looks like this:

Book1
	A	B	C	D
1	PART_NAME
2	RX.014ACCULINKII8/30FDA
3	RX.014ACCULINKII7-10/40FDA
4	RX.014ACCULINKII6-8/40CE
5	RX.014ACCULINKII6-8/40CE
6	RX.014ACCULINKII7-10/40CE
7	RX.014ACCULINKII7-10/30FDA
8	RX.014ACCULINKII7-10/30FDA
9	RX.014ACCULINKII7-10/40FDA
10	RX.014ACCULINKII7-10/40CE
11	RX.014ACCULINKII9/30FDA
12	RX.014ACCULINKII7-10/40FDA
13	RX.014ACCULINKII7-10/40CE
14	RX.014ACCULINKII6-8/30FDA
Sheet1

I am looking to extract only the numbers in the last portion of the data in these fields.

for example, in (A2) RX .014 ACCULINK II 8/30 FDA I really only need the 8/30. I could always go with the simple =right(A2,8). However, looking at the multiple variations in this column, I really want to find out if there is a more complex formula to extract only the numbers. For example if I could get the 8 in one column and the 30 in another, as one is diameter and one is length.
But I'd be happy with being able to extract only the 8/30 because I can always do a text to columns based off the / later.

Please help if you can.

Thanks

 

[barry houdini]

Try this formula in B2 copied down

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"-"," ")," ",REPT(" ",40)),80),40))

 

[Domski]

Hi,

Try this in B2:

=MID(A2,SEARCH("II ",A2)+3,SEARCH("/",A2,1)-(SEARCH("II ",A2)+3))

And this in C2:

=MID(A2,SEARCH("/",A2,1)+1,SEARCH(" ",A2,SEARCH("II ",A2)+3)-(SEARCH("/",A2,1)+1))

And copy them down.

 

[aka_krakur]

That worked great for exactly what I was asking for; however, I didn't look at the scenarios where in the same data (take cell A8 for example) it has RX .014 ACCULINK II 7-10/30 FDA where I would also need the 7-
so for A8 I would need 7-10/30 extracted.
Is that possible?

 

[aka_krakur]

domski,
yours was exactly what I was looking for.
Thanks

 

[Richard Schollar]

Hi

In case you're interested, the following is a UDF that will also do what you want:

Public Function GetNum(s As String) As String
Dim regex As Object, aMatch
Set regex = CreateObject("VBScript.regexp")
With regex
    .Pattern = "[0-9\-]+/[0-9]+"
    .Global = True
    Set aMatch = .Execute(s)
End With
GetNum = aMatch(0)
End Function

Use in an Excel cell as normal ie:

=GetNum(A1)

Best Regards

Richard

 

[aka_krakur]

I like that one the best. I like scripts beter than formulas. Thanks
It works great.