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?

Actually the example (and it was only an example) was overly
simplistic. I am trying to remove external references from a batch of
files I am creating on the fly. I want to remove external references
=[aBorders.xls]Assumptions!D$2*M2 So get rid of [aBorders.xls]
The following code works well in most instances.

Sub ExtRef_Remover()

Dim cell As Range, n As Variant

For Each cell In ActiveSheet.Cells.SpecialCells(xlFormulas)
n = Application.Find("]", cell.Formula)
If Not IsError(n) Then
'"='" Change when split name is used.
cell.Formula = "=" & Right(cell.Formula, Len(cell.Formula)
- n)
End If
Next cell

End Sub

However if the user in their infinite wisdom decide to model their
formula as such =1000 +[aBorders.xls]Assumptions!D$2*M2

The above will take the 1000 part out.

Don's answer worked for my overly simplistic example but when I got
into work today the real deal made sure it would not work.

Chad

On Sun, 4 Jul 2010 16:53:44 -0700 (PDT), marcus
wrote:

Actually the example (and it was only an example) was overly
simplistic. I am trying to remove external references from a batch of
files I am creating on the fly. I want to remove external references
=[aBorders.xls]Assumptions!D$2*M2 So get rid of [aBorders.xls]
The following code works well in most instances.

Sub ExtRef_Remover()

Dim cell As Range, n As Variant

For Each cell In ActiveSheet.Cells.SpecialCells(xlFormulas)
n = Application.Find("]", cell.Formula)
If Not IsError(n) Then
'"='" Change when split name is used.
cell.Formula = "=" & Right(cell.Formula, Len(cell.Formula)
- n)
End If
Next cell

End Sub

However if the user in their infinite wisdom decide to model their
formula as such =1000 +[aBorders.xls]Assumptions!D$2*M2

The above will take the 1000 part out.

Don's answer worked for my overly simplistic example but when I got
into work today the real deal made sure it would not work.

Chad


Try:

===========================
Option Explicit
Sub ExtRef_Remover()
Dim cell As Range, n As Variant
Dim re As Object
Const sPat As String = "\[[^]]*]"
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
cell.Formula = re.Replace(cell.Formula, "")
Next cell
End Sub
=========================

Hey Ron

That worked well. Thanks a lot.

Thanks too everyone for your help.

Chad

On Sun, 4 Jul 2010 18:15:04 -0700 (PDT), marcus
wrote:

Hey Ron

That worked well. Thanks a lot.

Thanks too everyone for your help.

Chad

You're welcome. Glad to help. Thanks for the feedback.