On 04/07/2010 13:26, Don Guillett Excel MVP wrote:
On Jul 4, 7:15 am, Don Guillett Excel
wrote:
On Jul 4, 5:07 am, wrote:





Hi

I wanted to remove the brackets and text within them using vba. In
each cell I have the item;

There [has to be an answer] it is.

Where the result after running the process is ;

There it is.

I have been searching for hours and can't seem to find anything that
specifically gets this done.

Any help or pointers greatly appreciated.

Chad

=LEFT(C16,FIND("[",C16)-1)& RIGHT(C16,LEN(C16)-FIND("]",C16)-1)- Hide quoted text -

- Show quoted text -
You did say VBA

Option Explicit
Sub clearmidtextSAS()
Dim c As Range
Dim p1 As Double
Dim p2 As Double
For Each c In Range("c16:c16") 'With ActiveCell
p1 = InStr(c, "[")
'MsgBox p1
p2 = InStr(c, "]")
'MsgBox p2
c.Value = Left(c, p1 - 1)& Right(c, Len(c) - p2 - 1)
Next c
End Sub

Don,

Out of curiosity why are p1 7 p2 dimmed as double rather than long?