Extracting info from a string using VBA plz

[Guest]

Hello everyone!!

Need to create a custum function in Excel that will allow me to extra
a series of numbers from a string.

The string will have a fix format, so this should facilitate the process.
The numbers im looking for are always going to be serounded by brakets.

Ex: test(234)test2

From this example and using the brakets as guidlines, what type of code would
be able to extract the '234' number.

Thx for your hlp,
Patrick

 

[Guest]

Why VBA? This formula should do what you need. It assumes the text is in Cell
A1

=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))

 

[Guest]

Patrick,
You colud use the following formula:

=MID(A2,FIND("(",A2,1)+1,FIND(")",A2,1)-FIND("(",A2,1)-1)

Assuming data starts in A2, place in required column and copy down.

OR

=findNum(b1)

Function findNum(rng)

findNum = Mid(rng, InStr(1, rng, "(") + 1, InStr(1, rng, ")") - InStr(1,
rng, "(") - 1)
End Function

 

[Guest]

That will work for me to, thx alot Jim.....

Why VBA? This formula should do what you need. It assumes the text is in Cell
A1

=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))

 

[Guest]

Patrick. The difference between Topper's formula and mine is that mine
returns a number, while Topper's returns a string. Since you did not specify
just pick the one that works for you...

 

[JE McGimpsey]

No real need for code:

=--MID(LEFT(A1,FIND(")",A1)-1),FIND("(",A1)+1,255)

where the -- coerces the Text returned by MID() to an XL number.

However, if you need a UDF:

Public Function ExtractNumberInParens( _
ByVal sInput As String) As Variant
Dim sTemp As String
If sInput Like "*(*)*" Then
sTemp = Mid(Left(sInput, InStr(sInput, ")") - 1), _
InStr(sInput, "(") + 1)
If IsNumeric(sTemp) Then
ExtractNumberInParens = CDbl(sTemp)
End If
End If
If IsEmpty(ExtractNumberInParens) Then _
ExtractNumberInParens = CVErr(xlErrValue)
End Function