Seperating Numbers
 

Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell would be
"64").
Now, I would like to have all of these numbers to be sorted and separated in
one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

You don't need VBA for this. But it does take several steps:

1. use Data Text to Columns... with the plus sign as the separator
2. use Copy/PasteSpecial as transpose to copy the row of data into a column
4. sort the column
5. use copy/PasteSpecial as transpose to copy the column back into the row.
--
Gary's Student


"2007-User" wrote:

Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell would be
"64").
Now, I would like to have all of these numbers to be sorted and separated in
one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

Thanks,but do I have to do this every time I create such Cell? I mean, this
is a routine calculation that I should do, I have to enter all of those
numbers in one cell but at the same time, I need a procedure that can divide
them( as I explained or you explained ...) so I can do another type of
calculations base on each numbers.


"Gary''s Student" wrote in message
...
You don't need VBA for this. But it does take several steps:

1. use Data Text to Columns... with the plus sign as the separator
2. use Copy/PasteSpecial as transpose to copy the row of data into a
column
4. sort the column
5. use copy/PasteSpecial as transpose to copy the column back into the
row.
--
Gary's Student


"2007-User" wrote:

Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell would
be
"64").
Now, I would like to have all of these numbers to be sorted and separated
in
one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

Why not enter them in separate cells to begin with, the use the sum function
to add them up. Then you won't need to separate them.

--
Regards,
Tom Ogilvy

"2007-User" wrote in message
...
Thanks,but do I have to do this every time I create such Cell? I mean,
this is a routine calculation that I should do, I have to enter all of
those numbers in one cell but at the same time, I need a procedure that
can divide them( as I explained or you explained ...) so I can do another
type of calculations base on each numbers.


"Gary''s Student" wrote in
message ...
You don't need VBA for this. But it does take several steps:

1. use Data Text to Columns... with the plus sign as the separator
2. use Copy/PasteSpecial as transpose to copy the row of data into a
column
4. sort the column
5. use copy/PasteSpecial as transpose to copy the column back into the
row.
--
Gary's Student


"2007-User" wrote:

Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell
would be
"64").
Now, I would like to have all of these numbers to be sorted and
separated in
one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

This is what I am doing now but, these numbers (cells) are existing (and I
have many of those on each files !) , now I have to do some additional
calculation by using those numbers.


"Tom Ogilvy" wrote in message
...
Why not enter them in separate cells to begin with, the use the sum
function to add them up. Then you won't need to separate them.

--
Regards,
Tom Ogilvy

"2007-User" wrote in message
...
Thanks,but do I have to do this every time I create such Cell? I mean,
this is a routine calculation that I should do, I have to enter all of
those numbers in one cell but at the same time, I need a procedure that
can divide them( as I explained or you explained ...) so I can do another
type of calculations base on each numbers.


"Gary''s Student" wrote in
message ...
You don't need VBA for this. But it does take several steps:

1. use Data Text to Columns... with the plus sign as the separator
2. use Copy/PasteSpecial as transpose to copy the row of data into a
column
4. sort the column
5. use copy/PasteSpecial as transpose to copy the column back into the
row.
--
Gary's Student


"2007-User" wrote:

Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell
would be
"64").
Now, I would like to have all of these numbers to be sorted and
separated in
one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

if using VBA, select all the range you want to change first, then run test1
below.
but the range should be in same column.(i assume all formula are in the same
column and all oprators are "+")

Sub test1()
Dim r, s
Dim i As Long
For Each r In Selection
Range(r(1, 2), r.End(xlToRight)).ClearContents
s = Split(Mid(r.Formula, 2), "+")
For i = 0 To UBound(s)
r(1, 2 + i) = s(i)
Next
Next
End Sub

keizi

"2007-User" wrote in message
...
Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell would
be "64").
Now, I would like to have all of these numbers to be sorted and separated
in one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

That is it, thank you so much, is it possible to make it like one of Excel's
functions? (like SEPARATOR(A1))


"kounoike" wrote in message
...
if using VBA, select all the range you want to change first, then run
test1 below.
but the range should be in same column.(i assume all formula are in the
same column and all oprators are "+")

Sub test1()
Dim r, s
Dim i As Long
For Each r In Selection
Range(r(1, 2), r.End(xlToRight)).ClearContents
s = Split(Mid(r.Formula, 2), "+")
For i = 0 To UBound(s)
r(1, 2 + i) = s(i)
Next
Next
End Sub

keizi

"2007-User" wrote in message
...
Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell would
be "64").
Now, I would like to have all of these numbers to be sorted and separated
in one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

You could use something like :
Public Function GetElement(argInput As Range, Delim As String, Element As
Long) As Variant
GetElement = Split(argInput.Formula, Delim)(Element - 1)
End Function

You need to remove the initial "=" and add a check for a Formula and/or
Text/Value.

But as Tom says, it would easier to start with the data in separate columns
and just SUM to get the total, rather than the other way around.

NickHK

"2007-User" wrote in message
...
That is it, thank you so much, is it possible to make it like one of
Excel's
functions? (like SEPARATOR(A1))


"kounoike" wrote in message
...
if using VBA, select all the range you want to change first, then run
test1 below.
but the range should be in same column.(i assume all formula are in the
same column and all oprators are "+")

Sub test1()
Dim r, s
Dim i As Long
For Each r In Selection
Range(r(1, 2), r.End(xlToRight)).ClearContents
s = Split(Mid(r.Formula, 2), "+")
For i = 0 To UBound(s)
r(1, 2 + i) = s(i)
Next
Next
End Sub

keizi

"2007-User" wrote in message
...
Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell
would
be "64").
Now, I would like to have all of these numbers to be sorted and
separated
in one row (or column) next to each other (like "4","6","12", ... Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.

Seperating Numbers
 

Thanks NickHk !

"NickHK" wrote in message
...
You could use something like :
Public Function GetElement(argInput As Range, Delim As String, Element As
Long) As Variant
GetElement = Split(argInput.Formula, Delim)(Element - 1)
End Function

You need to remove the initial "=" and add a check for a Formula and/or
Text/Value.

But as Tom says, it would easier to start with the data in separate
columns
and just SUM to get the total, rather than the other way around.

NickHK

"2007-User" wrote in message
...
That is it, thank you so much, is it possible to make it like one of
Excel's
functions? (like SEPARATOR(A1))


"kounoike" wrote in message
...
if using VBA, select all the range you want to change first, then run
test1 below.
but the range should be in same column.(i assume all formula are in the
same column and all oprators are "+")

Sub test1()
Dim r, s
Dim i As Long
For Each r In Selection
Range(r(1, 2), r.End(xlToRight)).ClearContents
s = Split(Mid(r.Formula, 2), "+")
For i = 0 To UBound(s)
r(1, 2 + i) = s(i)
Next
Next
End Sub

keizi

"2007-User" wrote in message
...
Hi,

This is an example of what I am trying to do:
In one cell I have "=4+6+12+18+4+5+8+7" (so the result of this cell
would
be "64").
Now, I would like to have all of these numbers to be sorted and
separated
in one row (or column) next to each other (like "4","6","12", ...
Etc.)
how is this possible with or without using VBA?

Thanks for your future inputs.