extracting numbers only from columns

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I have a column in a worksheet that looks like this:
Book1
ABCD
1PART_NAME
2RX.014ACCULINKII8/30FDA
3RX.014ACCULINKII7-10/40FDA
4RX.014ACCULINKII6-8/40CE
5RX.014ACCULINKII6-8/40CE
6RX.014ACCULINKII7-10/40CE
7RX.014ACCULINKII7-10/30FDA
8RX.014ACCULINKII7-10/30FDA
9RX.014ACCULINKII7-10/40FDA
10RX.014ACCULINKII7-10/40CE
11RX.014ACCULINKII9/30FDA
12RX.014ACCULINKII7-10/40FDA
13RX.014ACCULINKII7-10/40CE
14RX.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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this formula in B2 copied down

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

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Active Member
Joined
Jan 31, 2006
Messages
438
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

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

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

Code:
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

Active Member
Joined
Jan 31, 2006
Messages
438
I like that one the best. I like scripts beter than formulas. Thanks
It works great.
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top