Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delimited data in column
Column F has data in the following format:
Column F 1.01 0.94 1.44,0.22 3.03 1.87,0.05 3.99 2.74,0.15,1.68,0.09 0.76 3.39 20.14 Where there are 2 or more numbers in each cell I need to insert a row(s). I then need to put all the numbers into the column and then total the cell into column E. The finished data would look like this: E F 1.01 1.01 0.94 0.94 1.66 1.44 0.22 3.03 3.03 1.92 1.87 0.05 3.99 3.99 4.66 2.74 0.15 1.68 0.09 0.76 0.76 3.39 3.39 20.14 20.14 Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delimited data in column
Gareth
Try this Sub DelimitedCols() Dim DelList As Variant Dim i As Long Dim CellRw As Long Dim sh As Worksheet Dim Ttl As Double Set sh = ThisWorkbook.Sheets(1) For CellRw = 12 To 3 Step -1 DelList = Split(sh.Range("F" & CellRw).Value, ",") Ttl = DelList(LBound(DelList)) If UBound(DelList) LBound(DelList) Then For i = LBound(DelList) + 1 To UBound(DelList) sh.Range("F" & CellRw).Offset(1, 0).EntireRow.Insert sh.Range("F" & CellRw).Offset(1, -1).Value = DelList(i) Ttl = Ttl + DelList(i) Next i sh.Range("F" & CellRw).Value = DelList(LBound(DelList)) sh.Range("F" & CellRw).Offset(0, -1).Value = Ttl Else sh.Range("F" & CellRw).Offset(0, -1).Value = _ sh.Range("F" & CellRw).Value End If Next CellRw End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "gareth" wrote in message ... Column F has data in the following format: Column F 1.01 0.94 1.44,0.22 3.03 1.87,0.05 3.99 2.74,0.15,1.68,0.09 0.76 3.39 20.14 Where there are 2 or more numbers in each cell I need to insert a row(s). I then need to put all the numbers into the column and then total the cell into column E. The finished data would look like this: E F 1.01 1.01 0.94 0.94 1.66 1.44 0.22 3.03 3.03 1.92 1.87 0.05 3.99 3.99 4.66 2.74 0.15 1.68 0.09 0.76 0.76 3.39 3.39 20.14 20.14 Thanks in advance. Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data csv file - tab delimited - and sort to column | Excel Discussion (Misc queries) | |||
entire column to comma delimited cell | Excel Discussion (Misc queries) | |||
How can I convert tab delimited files to pipe delimited? | Excel Discussion (Misc queries) | |||
Convert column data to semicolon delimited text string | Excel Worksheet Functions | |||
Import Comma delimited info in a Column | Excel Worksheet Functions |