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 :)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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
50,553
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.
 

Forum statistics

Threads
1,141,167
Messages
5,704,678
Members
421,361
Latest member
Capper2824

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top