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