# extracting numbers only from columns

#### aka_krakur

##### Active Member
I have a column in a worksheet that looks like this:
Book1
ABCD
1PART_NAME
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.

Thanks

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### barry houdini

##### MrExcel MVP
Try this formula in B2 copied down

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

#### Domski

##### Well-known Member
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
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
domski,
yours was exactly what I was looking for.
Thanks

#### Richard Schollar

##### MrExcel MVP
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
I like that one the best. I like scripts beter than formulas. Thanks
It works great.

Replies
4
Views
910
Replies
14
Views
481
Replies
8
Views
296
Replies
9
Views
366
Replies
3
Views
424

1,171,967
Messages
5,878,534
Members
433,348
Latest member
eramirez148

### 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.

### Which adblocker are you using?

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

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