Many values to separate into each row?

cubbear

New Member
Joined
Oct 22, 2004
Messages
16
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 :)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

njbisp

New Member
Joined
Oct 18, 2006
Messages
5
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

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

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
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

New Member
Joined
Oct 22, 2004
Messages
16

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
13.4,4.5,6,73.4
24.5,6,74.5
36,76
477
5  
6  
Convert to Column
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130

ADVERTISEMENT

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

Board Regular
Joined
Jun 1, 2003
Messages
130
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,599
Messages
5,541,166
Members
410,543
Latest member
ExcelGlenn
Top