This is great information and an excellent explenation as well. I
truly appreciate the time involved to explain this!

My pleasure... I was glad to do it.

Rick


"Rick S." wrote in message
...
This is great information and an excellent explenation as well. I truly
appreciate the time involved to explain this!
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick Rothstein (MVP - VB)" wrote:

Let's show you what I did with an example. Consider this string of
text...

TextFromCell = "One two (Three Four) Five (Six) Seven"

First off, since the Split function only works with a single delimiter,
let
convert the closing parentheses to opening parentheses, but in such a way
that we can find them again later in order to turn them back to closing
parentheses. To do that, I am going to replace all ')' with '()'....

' TextLine is Dim'med as a simple String
TextLine = Replace(TextFromCell, ")", "()")

At this point, TextLine contains this...

"One two (Three Four() Five (Six() Seven"

Now, we split this using the open parenthesis as the delimiter.

' ParsedLine is Dim'med as a dynamic String array
ParsedLine = Split(TextLine, "(")

Okay, at this point the ParsedLine array has 5 elements (index numbers 0
through 4)

Element 0: "One two "
Element 1: "Three Four"
Element 2: ") Five "
Element 3: "Six"
Element 4: ") Seven"

Notice that text inside the parentheses are located at elements 1 and 3.
As
it turns out, no matter how many parentheses-grouped pieces of text you
have, they will always occur at an odd-numbered element index... even if
the
text starts with an open parenthesis (with no text in front of it). So,
to
process only the text **not** located inside parentheses, all we have to
do
is loop through the even numbered element indexes starting with index
number
zero. The loop structure to do that is...

For X = 0 To UBound(ParsedLine) Step 2
'
' ParsedLine(X) is text not inside any parentheses, do something
to
it here
'
Next

Okay, now the elements of the array look like this (assuming we are upper
casing it)...

Element 0: "ONE TWO "
Element 1: "Three Four"
Element 2: ") FIVE "
Element 3: "Six"
Element 4: ") SEVEN"

Now, we rejoin the array using the Join function and specify the opening
parenthesis (what we used to break it the original text apart with) as
the
delimiter. Once this is done, our joined text string looks like this...

TextLine = "ONE TWO (Three Four() FIVE (Six() SEVEN"

All that is left is to replace the '()' symbol pair with ')" and assign
it
back to the cell where it came from...

TextFromCell = Replace(TextLine, "()", ")")

Now we do this for every cell in the range we are processing.

Rick


"Rick S." wrote in message
...
If and when you have the time, a brief description of your edits would
be
appreciated!
So much so, I will personally drink a beer on your behalf. :)
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick Rothstein (MVP - VB)" wrote:

I do not understand how you did that,

If you are familiar with VBA, do you want an explanation?

but it works great!!!

I'm glad it worked out for you (I was a little worried that ND Pard's
response might have kept you from looking back into this thread in
order
to
see it).

Rick