I am working on an Excel sheet which has Amounts added in it and I want to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter Key
Stroke" So as to push the Values to next cell is is possible

I am working on an Excel sheet which has Amounts added in it and I want to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

Does it have to be a formula or macro? Or are you just looking for a method
to break the cell up into individual cells one time?

If you need a formula or code solution, are the operations always additions
or could there be other math operations mixed in (multiplication,
subtraction, division, other)? Can the number of items being "added" vary?
Or will there always be 5 of them?

Here is the manual method to do what you asked. Select A1 and click
Data/TextToColumns from Excel's menu (if the cells next to A1 have data in
them, you will need to copy the contents of A1 to a cell where the cells to
its right are empty and do the following to that cell instead). On the
dialog that appears, select the Delimited option and click Next. Check the
CheckBox labeled "Other" and put a plus (+) sign in the fill-in field next
to it. Click the Finish button. Now, on the spreadsheet, select all of the
cells that were created and Copy them into the Windows Clipboard (Ctrl+C)
and then click into the A2 cell and select Edit/PasteSpecial from Excel's
menu, Now, put a check in the CheckBox labeled "Transpose" and click OK.
This will copy the cells to where you wanted. Finally, delete the cells in
row 1 that were created from A1.

Rick

It's not pretty, but I got it working - Paste this into a Standard module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1), Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I want to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter Key
Stroke" So as to push the Values to next cell is is possible

No need to duplicate the manual method in code. Assuming the math operations
will always be addition (a question I have ask the OP to clarify), you could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1), Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter Key
Stroke" So as to push the Values to next cell is is possible

Rick,

That is pretty darn neat;
Thanks for furnishing..

Jim

"Rick Rothstein (MVP - VB)" wrote:

No need to duplicate the manual method in code. Assuming the math operations
will always be addition (a question I have ask the OP to clarify), you could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1), Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter Key
Stroke" So as to push the Values to next cell is is possible

Another variation along this theme.

Sub Demo()
Dim v
v = Split(Mid$([A1].Formula, 2), "+")
[A2].Resize(UBound(v) + 1).Value = _
WorksheetFunction.Transpose(v)
End Sub

--
Dana DeLouis


"JMay" wrote in message
...
Rick,

That is pretty darn neat;
Thanks for furnishing..

Jim

"Rick Rothstein (MVP - VB)" wrote:

No need to duplicate the manual method in code. Assuming the math
operations
will always be addition (a question I have ask the OP to clarify), you
could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard
module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False,
Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1),
Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I
want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter
Key
Stroke" So as to push the Values to next cell is is possible

Crap, you guys keep "raising-the-bar" ---------------
Thanks Dana - "really"

"Dana DeLouis" wrote:

Another variation along this theme.

Sub Demo()
Dim v
v = Split(Mid$([A1].Formula, 2), "+")
[A2].Resize(UBound(v) + 1).Value = _
WorksheetFunction.Transpose(v)
End Sub

--
Dana DeLouis


"JMay" wrote in message
...
Rick,

That is pretty darn neat;
Thanks for furnishing..

Jim

"Rick Rothstein (MVP - VB)" wrote:

No need to duplicate the manual method in code. Assuming the math
operations
will always be addition (a question I have ask the OP to clarify), you
could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard
module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False,
Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1),
Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I
want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter
Key
Stroke" So as to push the Values to next cell is is possible

Thanks. Although the following is not a "bar raiser" like Dana's code was, I
thought you might find it an interesting extension to our previous postings.

Because the OP's original cell contained a summation, we can assume he will
likely add up the column of numbers that are created by our code. Since it
is possible for a summation to include negative numbers, I thought it would
be a good idea to extend the code to handle them. I am thinking of an
original cell content something like this...

=1500+500-300+100-500+900

where addition and subtraction are combined. A slight modification to one
line (it will also work for Dana's code) solves this problem. Here is my
revised code to handle plus an minus operations...

Dim X As Long
Dim Data() As String
Data = Split(Replace(Mid$(Range("A1").Formula, 2), "-", "+-"), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick



"JMay" wrote in message
...
Rick,

That is pretty darn neat;
Thanks for furnishing..

Jim

"Rick Rothstein (MVP - VB)" wrote:

No need to duplicate the manual method in code. Assuming the math
operations
will always be addition (a question I have ask the OP to clarify), you
could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard
module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False,
Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1),
Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I
want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter
Key
Stroke" So as to push the Values to next cell is is possible

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

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?

Thanks a million.....

Jim

"Rick Rothstein (MVP - VB)" wrote:

Thanks. Although the following is not a "bar raiser" like Dana's code was, I
thought you might find it an interesting extension to our previous postings.

Because the OP's original cell contained a summation, we can assume he will
likely add up the column of numbers that are created by our code. Since it
is possible for a summation to include negative numbers, I thought it would
be a good idea to extend the code to handle them. I am thinking of an
original cell content something like this...

=1500+500-300+100-500+900

where addition and subtraction are combined. A slight modification to one
line (it will also work for Dana's code) solves this problem. Here is my
revised code to handle plus an minus operations...

Dim X As Long
Dim Data() As String
Data = Split(Replace(Mid$(Range("A1").Formula, 2), "-", "+-"), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick



"JMay" wrote in message
...
Rick,

That is pretty darn neat;
Thanks for furnishing..

Jim

"Rick Rothstein (MVP - VB)" wrote:

No need to duplicate the manual method in code. Assuming the math
operations
will always be addition (a question I have ask the OP to clarify), you
could
use code something like this to do what the OP wants (again, assuming
**only** additions)...

Dim X As Long
Dim Data() As String
Data = Split(Mid$(Range("A1").Formula, 2), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

Rick


"JMay" wrote in message
...
It's not pretty, but I got it working - Paste this into a Standard
module.
Before running the Macro - Select your Cell A1 (your = 1500+....) cell.

Jim

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/7/2007 by Jim May
'

'
Dim rrow As Integer
Selection.TextToColumns Destination:=ActiveCell.Offset(1,
0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False,
Space:=False,
_
Other:=True, OtherChar:="+", FieldInfo:=Array(Array(1, 1),
Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.End(xlDown).Select
nrow = Range("A65536").End(xlUp).Row + 1
rrow = nrow - 3
rrow = CInt(rrow)
Range("A" & nrow).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rrow & "]C:R[-1]C)"
Range("A2").EntireRow.Delete
Range("A1").Select

End Sub

"claude jerry" wrote:

I am working on an Excel sheet which has Amounts added in it and I
want
to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

I dont know Much on VBA but was
trying to use Find Replace command to Replace all "+" with a "Enter
Key
Stroke" So as to push the Values to next cell is is possible

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

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

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

Rick - when I enter (as you suggest) the following (and step through), why am
I getting R/T error 9 - Subscript out of range on line 4?

Sub Tester()
Dim Array1() As String
Dim Array2() As String
Array1(0) = "Jim"
Array1(1) = "Paul"
End Sub

"Rick Rothstein (MVP - VB)" wrote:

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

Rick - when I enter (as you suggest) the following (and step through), why
am
I getting R/T error 9 - Subscript out of range on line 4?

Sub Tester()
Dim Array1() As String
Dim Array2() As String
Array1(0) = "Jim"
Array1(1) = "Paul"
End Sub

You dimensioned Array1 as a dynamic array (no number between the
parentheses) and did not subsequently ReDim it before trying to assign
values to its elements. Somewhere, either in a Dim statement (in which case
the array will be fixed, not dynamic) or in a ReDim statement (for
dynamically declared arrays), you have to tell VB how many elements you plan
to assign data to (VB needs to set aside memory locations to store the data
that is going to be assigned later on in the program).

Rick

Rick:

A final Thanks for all you've done (for me). My sudden problem with the R/T
9 overshadowed my thanking you for the added knowledge of the "JOIN".

Jim

"Rick Rothstein (MVP - VB)" wrote:

Rick - when I enter (as you suggest) the following (and step through), why
am
I getting R/T error 9 - Subscript out of range on line 4?

Sub Tester()
Dim Array1() As String
Dim Array2() As String
Array1(0) = "Jim"
Array1(1) = "Paul"
End Sub

You dimensioned Array1 as a dynamic array (no number between the
parentheses) and did not subsequently ReDim it before trying to assign
values to its elements. Somewhere, either in a Dim statement (in which case
the array will be fixed, not dynamic) or in a ReDim statement (for
dynamically declared arrays), you have to tell VB how many elements you plan
to assign data to (VB needs to set aside memory locations to store the data
that is going to be assigned later on in the program).

Rick

Thanks rick / Jay
IN fact I was looking for a Method to split the contents in different cells
apology for my posting error

AS you asked me , the cell has only + or - operators and no * or / in it.
I tried something more tedious and it worked for the time being
I copied the content of the cell i.e =1500+500+300+100+500 into a word file
there I used find "+" replace Special paragraph break
and deleted the = sign
and then copied the content and pasted in Excel.

But yours is a more simple method

thanks Rick / Jay

"Rick Rothstein (MVP - VB)" wrote:

I am working on an Excel sheet which has Amounts added in it and I want to
break it up into individual cells

Example in Cell A1 I have the following =1500+500+300+100+500
As such the final value shown in Cell A1 is 2900

Now I want this values to be splitted and shown in different cells
Eg A2 = 1500 , A3=500, A4=300, A5=100,A6=500 and Total in A7=2900

Does it have to be a formula or macro? Or are you just looking for a method
to break the cell up into individual cells one time?

If you need a formula or code solution, are the operations always additions
or could there be other math operations mixed in (multiplication,
subtraction, division, other)? Can the number of items being "added" vary?
Or will there always be 5 of them?

Here is the manual method to do what you asked. Select A1 and click
Data/TextToColumns from Excel's menu (if the cells next to A1 have data in
them, you will need to copy the contents of A1 to a cell where the cells to
its right are empty and do the following to that cell instead). On the
dialog that appears, select the Delimited option and click Next. Check the
CheckBox labeled "Other" and put a plus (+) sign in the fill-in field next
to it. Click the Finish button. Now, on the spreadsheet, select all of the
cells that were created and Copy them into the Windows Clipboard (Ctrl+C)
and then click into the A2 cell and select Edit/PasteSpecial from Excel's
menu, Now, put a check in the CheckBox labeled "Transpose" and click OK.
This will copy the cells to where you wanted. Finally, delete the cells in
row 1 that were created from A1.

Rick

IN fact I was looking for a Method to split the contents in different
cells
apology for my posting error

AS you asked me , the cell has only + or - operators and no * or / in it.
I tried something more tedious and it worked for the time being
I copied the content of the cell i.e =1500+500+300+100+500 into a word
file
there I used find "+" replace Special paragraph break
and deleted the = sign
and then copied the content and pasted in Excel.

But yours is a more simple method

I am presuming by "yours is more simple method", you mean this macro
routine...

Dim X As Long
Dim Data() As String
Data = Split(Replace(Mid$(Range("A1").Formula, 2), "-", "+-"), "+")
For X = 0 To UBound(Data)
Range("A1").Offset(X + 1, 0).Value = Data(X)
Next
Range("A1").Clear

that I last modified to handle plus and minus signs, right? The manual
method I first posted only works for a single user-specified delimiter and,
as such, would not handle both the plus and minus sign. I just want to be
clear on that as you posted your follow up in the manual method sub-thread
as opposed to the code method sub-thread.

Rick