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