# Extract number from parenthesis

#### excel_training_wheels

##### Board Regular
Hi--

Even though I have training wheels on, I almost have the formula I need, just need a bit of help.

What can I enter to extract the number, of varying length....

(111 @asb)
(123546 @hjd)
(2 @bbb)
(09 @tuy)

I was using the RIGHT function and getting close, but not exactly there. Thanks for your help!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
excel_training_wheels said:
Hi--

Even though I have training wheels on, I almost have the formula I need, just need a bit of help.

What can I enter to extract the number, of varying length....

(111 @asb)
(123546 @hjd)
(2 @bbb)
(09 @tuy)

I was using the RIGHT function and getting close, but not exactly there. Thanks for your help!

=SUBSTITUTE(LEFT(A2,SEARCH(" ",A2)-1),"(","")

#### Juan Pablo González

##### MrExcel MVP
A similar variation:

=VALUE(MID(LEFT(A3,FIND(" ",A3)-1),2,LEN(A3)))

#### excel_training_wheels

##### Board Regular
Aladin--you help me quite a bit. THANK you. That works great.

#### Peter100

##### Well-known Member
Hi
Ozgrid has a beautiful routine for this;
insert the following module

and then if you for example had 123frg567 in cell A1, just use the formula
=extractNumber(A1) wherever you want it

Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
l = l + 1
lNum = Mid(sText, lCount, 1) & lNum
End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End Function

Replies
16
Views
440
Replies
14
Views
495
Replies
4
Views
659
Replies
15
Views
577
Replies
23
Views
687

1,195,674
Messages
6,011,096
Members
441,582
Latest member
Topkapi

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