Many values to separate into each row?

[cubbear]

Hello All -

Is there a way that I can take a row that contains many values that are separated by commas and have them be placed in a separate row? For example if

3.4,4.5,6,7

I want it to be like so

3.4
4.5
6
7

Thank you for your help

 

[njbisp]

i am basically trying to fo te same thing except sererate by range ie. 1.0 to 2.0 and then total in groups at bottom..

 

[jindon]

Hi

UDF

1) hit Alt + F11 to open VB Editor
2) [Insert]-[Module] and paste the code
3) click x to close the window to get back to Excel

use in cell like

=IntoRows(A$1,",",Row(A1))

Then copy down

Function IntoRows(txt As String, delim As String, ref As Long)
Dim a
a = Split(txt, delim)
IntoRows = a(ref - 1)
End Function

 

[hsk]

This may not be the best way but have tried

Delimit on comma (Data -> Text to Column -> Delimited -> Other -> , )

Copy the row
and use 'Transpose' option in "paste special"

 

[cubbear]

Thanks!

jindon - When I do execute the "=" it just takes the first value. Does the code need to change into a loop?

hsk - thank you

 

[Peter_SSs]

Not all that neat but here is a possible formula approach:

A2 (copied down): =IF(ISNUMBER(FIND(",",A1)),SUBSTITUTE(A1,B1&",",""),"")
B1 (copied down): =IF(LEN(A1)>0,LEFT(A1,FIND(",",A1&",")-1)+0,"")

Mr Excel.xls
	A	B	C	D
1	3.4,4.5,6,7	3.4
2	4.5,6,7	4.5
3	6,7	6
4	7	7
5
6
Convert to Column

 

[larrydunn]

Here's what I came up with for a macro.

Sub BreakUpRow()
Break a row into multiple rows, breaking at comma
Dim vCellValue As Variant
Dim vThisValue As Variant
Dim iDet As String

vCellValue = ActiveCell.Value

FindComma:
iDet = InStr(vCellValue, ",") 'Find first comma
If iDet < 1 Then GoTo EndOfSubroutine
vThisValue = Left(vCellValue, iDet - 1)
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
ActiveCell.Value = vThisValue
vCellValue = Right(vCellValue, Len(vCellValue) - iDet) 'Remove first element
GoTo FindComma

EndOfSubroutine:
'Record last bit
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
If vCellValue <> "" Then ActiveCell.Value = vCellValue

End Sub

 

[larrydunn]

New Jersey,

This hasty bit of code goes part of the way toward your solution. It just lists the values in columns, but leaves the totals to you.

Sub BreakUpRowByValue()
'Break a row into multiple rows, breaking at comma
'Then express as a columnar array

Dim vCellValue As Variant
Dim vThisValue As Variant
Dim iDet As String
Dim sStartCell As String

sStartCell = ActiveCell.Address

vCellValue = ActiveCell.Value

FindComma:
iDet = InStr(vCellValue, ",") 'Find first comma
If iDet < 1 Then GoTo EndOfSubroutine
vThisValue = Left(vCellValue, iDet - 1)
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
ActiveCell.Value = vThisValue
vCellValue = Right(vCellValue, Len(vCellValue) - iDet) 'Remove first element
GoTo FindComma

EndOfSubroutine:
'Record last bit
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
If vCellValue <> "" Then ActiveCell.Value = vCellValue

ActiveSheet.Range(sStartCell).Select 'Point at original cell

NextCell:
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
vCellValue = ActiveCell.Value 'Get value to compare
If vCellValue = "" Then GoTo EndOfPart2

If Not IsNumeric(ActiveCell.Value) Then GoTo NextCell

If ActiveCell.Value >= 9 Then GoTo Plus9
If ActiveCell.Value >= 8 Then GoTo Plus8
If ActiveCell.Value >= 7 Then GoTo Plus7
If ActiveCell.Value >= 6 Then GoTo Plus6
If ActiveCell.Value >= 5 Then GoTo Plus5
If ActiveCell.Value >= 4 Then GoTo Plus4
If ActiveCell.Value >= 3 Then GoTo Plus3
If ActiveCell.Value >= 2 Then GoTo Plus2

Plus1:
ActiveCell.Next.Value = vCellValue
GoTo NextCell
Plus2:
ActiveCell.Next.Next.Value = vCellValue
GoTo NextCell
Plus3:
ActiveCell.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus4:
ActiveCell.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus5:
ActiveCell.Next.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus6:
ActiveCell.Next.Next.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus7:
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus8:
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell
Plus9:
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Next.Next.Value = vCellValue
GoTo NextCell

EndOfPart2:

End Sub

 

[jindon]

Thanks!

jindon - When I do execute the "=" it just takes the first value. Does the code need to change into a loop?

hsk - thank you

You need to copy down the forumla after you inserted to the cell.