finding the "(" in text

  • Thread starter Thread starter dstiefe
  • Start date Start date
D

dstiefe

I have cells that contain a brief sentence

in the sentence I have information that is surrounded by ( data )

how do I tell excel to copy the data between the ( copy this data )

Thank you
 
You can get the text within (but not including) the parentheses with a
formula like:


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

where A1 has the text.

Or, you can use VBA:

Function GetDataText(R As Range) As String
Dim S As String
Dim N As Long
Dim M As Long

S = R.Text
M = InStr(1, S, "(")
If M = 0 Then
Exit Function
End If
N = InStr(M, S, ")")
If N = 0 Then
Exit Function
End If
GetDataText = Mid(S, M + 1, N - M - 1)
End Function

and call this either from other VBA or from a worksheet cell with
=GetDataText(A1)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
can you use the first formul within vba code?

Chip Pearson said:
You can get the text within (but not including) the parentheses with a
formula like:


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

where A1 has the text.

Or, you can use VBA:

Function GetDataText(R As Range) As String
Dim S As String
Dim N As Long
Dim M As Long

S = R.Text
M = InStr(1, S, "(")
If M = 0 Then
Exit Function
End If
N = InStr(M, S, ")")
If N = 0 Then
Exit Function
End If
GetDataText = Mid(S, M + 1, N - M - 1)
End Function

and call this either from other VBA or from a worksheet cell with
=GetDataText(A1)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Click to expand...
 
last question...

i'm alwasy uncertain how to use functions

how do I pass the string to the function in my vba code

and how do I get the output?

make sense
 
I have cells that contain a brief sentence

in the sentence I have information that is surrounded by ( data )

how do I tell excel to copy the data between the ( copy this data )

Thank you
Click to expand...

Since this is a programming group, here is a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=BetweenParenth(A1)

in some cell where A1 contains the string you wish to parse.

===================================
Option Explicit
Function BetweenParenth(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "^[^(]*\(([^)]+).*$"
If re.test(s) = True Then
BetweenParenth = re.Replace(s, "$1")
End If
End Function
============================
--ron
 
You can't really use a formula within VBA code. The formula I posted
can be put into the cell in which you want the text within the
parentheses display. For example, if cell A1 has

This is some (data text) stuff

and B1 has the formula

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

cell B1 will display

data text

If you don't want to use a formula but would rather use a function
written in VBA, press ALT F11 to open the VBA editor, go to the Insert
menu, and choose Module. In that module, paste the VBA code I
provided. Close the VBA editor to return to Excel.

Now, you can enter

=GetDataText(A1)

in some cell and that cell will display the text within the
parentheses from cell A1.

The formula approach and the VBA approach have the same end result --
the text within the parentheses. Since you posted in the Programming
newsgroup, the presumption is that you want a VBA solution. That's
what I provided. However, since the same thing can be had with a
simple formula without using VBA, I provided that as an alternative.
Pick either the formula solution or the VBA solution. The result is
basically the same.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top