Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperating Text from Numbers in a cell | Excel Worksheet Functions | |||
excel, seperating numbers and text, macro | Excel Discussion (Misc queries) | |||
Seperating numbers from text | Excel Discussion (Misc queries) | |||
Seperating First and Last Names | Excel Discussion (Misc queries) | |||
Seperating Numbers from Letters in Excel | Excel Worksheet Functions |