Extract number from parenthesis

[excel_training_wheels]

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!

 

[Aladin Akyurek]

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]

A similar variation:

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

 

[excel_training_wheels]

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

 

[Peter100]

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