You are quite welcome... it was my pleasure.

While you are fooling around with the Split function to learn all the neat
things you can do with it, you should also look at it "inverse companion"
function... Join. The Join function does just the opposite of Split... it
takes a one-dimensional Variant (containing elements that can be converted
to Strings) or String array and joins all the elements into a single String
of text using a delimiter that you specify to link them. For example, if you
had this array...

Arr(0) = "One"
Arr(1) = "Two"
Arr(2) = "Three"

and wanted to join them together with, say, double slashes between them (the
delimiter for Split and Join do NOT have to be single characters)...

CombinedElements = Join(Arr, "//")

which would assign One//Two//Three to the variable.

I just thought you might find the above interesting.

Rick



"JMay" wrote in message
...
Sir Rick:
You are an EXCELLANT teacher !!
Thank you very much.
Jim May


"Rick Rothstein (MVP - VB)" wrote:

Rick, Thanks -- This Split function (VBA only) is working a miricle.
Data() is obvioulsy an string array that is catching the Split results

Correct. The parentheses (brackets to some of you out there) are
optional,
just like they are when assigning one array to the other. The reason is
Split returns an array. So, just like you can do this...

Dim Array1() As <Whatever
Dim Array2() As <Whatever
Array1(0) = <something
Array1(1) = <something else
etc.
Array2 = Array1

That last line could also be written Array2() = Array1() if you want.
Now,
since Split returns an array (and it does all the behind the scenes
assignments to the various elements, you can do either of the
following....

SomeDynamicArray = Split(<<arguments)

or

SomeDynamicArray() = Split(<<arguments)

It's your choice.


The only part that I can not get my head around is:
the replace portion..

Is replace llooking for "-" (a minus character) and replacing all with
a "+-" set of characters then the comma? and then the "+"

Can you verbalize what's going on here?

Maybe it will help if we separate the arguments into individual
statements.
The relevant statements to your question are these...

Dim Data() As String
Data = Split(Replace(Mid$(Range("A1").Formula, 2), "-", "+-"), "+")

But the last line is just a function call within a function call. Split
apart, code-wise, it would look like this...

Dim DataLine As String
Dim ModifiedDataLine As String
Dim Data() As String
DataLine = Mid$(Range("A1").Formula, 2)
ModifiedDataLine = Replace(DataLine , "-", "+-")
Data = Split(ModifiedDataLine, "+")

What we are doing is assigning everything from the formula in A1, except
for
the leading equal sign, to the String variable DataLine. We then replace
all
occurrences of a minus sign with a plus sign followed by a minus sign
(remember, the contents of DataLine is a string of text, nothing more)
and
assign it to the variable ModifiedDataLine. Finally, we take the modified
data line and Split it using the plus sign as the delimiter. For the
negative values... they became +-number and, when the delimiting plus
sign
is removed in the Split process, the minus sign remains. In order to
handle
the negative terms, which just consisted of a minus sign, we had to
modify
it so there was a plus sign coupled with it to give the term something to
be
Split on.

I hope that was all clear... it is usually easier to visualize these
things
in one's head then it is to verbalize the thought process behind it (at
least for me it is).


Rick