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

On Jul 4, 5:07*am, Chad 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)

On Jul 4, 7:15*am, Don Guillett Excel MVP
wrote:
On Jul 4, 5:07*am, Chad 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

Hi Don

Thanks so much for your time. Yep I was after a vb solution. Thank
you so much for your help. Your code worked fantastically well.

Take care

Chad

In message
..com of Sun, 4 Jul 2010 03:07:14 in microsoft.public.excel.programming,
Chad writes
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.

Your example shows losing a space as well as what you specify.
You do not say what you want to happen if there is more than one match.
In a VBA window, Ctrl+G opens an immediate window. In that window, I
have
?getsub("There [has to be an answer] it is.", "\[[^]]*] *", "")
There it is.

The interesting thing in that is a matching Regular Expression (RE)
which is "\[[^]]*] *"
This is a sequence of the following elements:
"\[" A literal opening square bracket;
"[^]]*" A character class [...] consisting of a character which is not a
closing square bracket "^]", 0 or more times "*";
"]" A closing square bracket;
" *" A space 0 or more times.

At this point, you will be wondering where "getsub" comes from. It is a
function which matches an RE and substitutes a result. I might have this
function:
Private Function GetSub(ByVal from As String, _
ByVal Match As String, _
ByVal Part As String) As String
Dim RE As Object
Set RE = CreateObject("VBScript.Regexp")
RE.Global = True ' Comment this for no more than 1 match

RE.Pattern = Match
GetSub = RE.Replace(from, Part)
Set RE = Nothing
End Function

In my implementation, RE is declared at module level, RE and RE.Global
are set in a module initialisation subroutine and RE = Nothing is in a
module closing function.

Getsub is a general purpose function which does most of the text
substitutions in my code. You might want to read
<http://msdn.microsoft.com/en-us/library/ms974570.aspx
to learn about Regular Expressions.
It is not an easy topic; IMHO, it does repay learning.

Please let the newsgroup know how this works for you!
--
Walter Briscoe

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.

If you need a macro, you could try this when you do it manually:

Select the range to fix
Edit|replace
what: _[*] (the underscore represents the space character)
with: (leave blank)
replace all



On 07/04/2010 05:07, Chad 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


--
Dave Peterson