Home |
Search |
Today's Posts |
#1
|
|||
|
|||
parse cell and insert rows?
Is there a way in Excel to parse a cell that has a comma delimited
values, and put each value into rows? In this situation, the cell with the delimited values can have a varying amount of values - and because of formatting issues, I would like to insert new rows. Example: CELL A1: 10,11,12 CELL A2: 10 CELL A3: 11 CELL A4: 12 Any help would be greatly appreciated! /grod |
#2
|
|||
|
|||
You can use text to columns to parse the string, then copy/paste
specialtranspose to get them into rows. "Gorrila Grod" wrote in message news:2004110210394316807%grod@nothankscom... Is there a way in Excel to parse a cell that has a comma delimited values, and put each value into rows? In this situation, the cell with the delimited values can have a varying amount of values - and because of formatting issues, I would like to insert new rows. Example: CELL A1: 10,11,12 CELL A2: 10 CELL A3: 11 CELL A4: 12 Any help would be greatly appreciated! /grod |
#3
|
|||
|
|||
You could use a little macro:
Option Explicit Sub testme() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Dim mySplit As Variant Dim myStr As String Dim NumberOfElements As Long Set wks = Worksheets("sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 myStr = .Cells(iRow, "A").Value If Len(myStr) 0 Then mySplit = Split97(myStr, ",") NumberOfElements = UBound(mySplit) - LBound(mySplit) + 1 If NumberOfElements 1 Then .Cells(iRow, "A").Resize(NumberOfElements - 1) _ .EntireRow.Insert .Cells(iRow, "A").Resize(NumberOfElements).Value _ = Application.Transpose(mySplit) End If End If Next iRow End With End Sub 'from Tom Ogilvy Function Split97(sStr As Variant, sdelim As String) As Variant Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function ======== If you're using xl2k or higher, change this line from: mySplit = Split97(myStr, ",") to: mySplit = Split(myStr, ",") And you can delete the split97 function, too. Split was added in xl2k. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gorrila Grod wrote: Is there a way in Excel to parse a cell that has a comma delimited values, and put each value into rows? In this situation, the cell with the delimited values can have a varying amount of values - and because of formatting issues, I would like to insert new rows. Example: CELL A1: 10,11,12 CELL A2: 10 CELL A3: 11 CELL A4: 12 Any help would be greatly appreciated! /grod -- Dave Peterson |
#4
|
|||
|
|||
Awesome! Thank you!
and thanks for the link on macros! On 2004-11-02 17:57:59 -0700, Dave Peterson said: You could use a little macro: Option Explicit Sub testme() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Dim mySplit As Variant Dim myStr As String Dim NumberOfElements As Long Set wks = Worksheets("sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 myStr = .Cells(iRow, "A").Value If Len(myStr) 0 Then mySplit = Split97(myStr, ",") NumberOfElements = UBound(mySplit) - LBound(mySplit) + 1 If NumberOfElements 1 Then .Cells(iRow, "A").Resize(NumberOfElements - 1) _ .EntireRow.Insert .Cells(iRow, "A").Resize(NumberOfElements).Value _ = Application.Transpose(mySplit) End If End If Next iRow End With End Sub 'from Tom Ogilvy Function Split97(sStr As Variant, sdelim As String) As Variant Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function ======== If you're using xl2k or higher, change this line from: mySplit = Split97(myStr, ",") to: mySplit = Split(myStr, ",") And you can delete the split97 function, too. Split was added in xl2k. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gorrila Grod wrote: Is there a way in Excel to parse a cell that has a comma delimited values, and put each value into rows? In this situation, the cell with the delimited values can have a varying amount of values - and because of formatting issues, I would like to insert new rows. Example: CELL A1: 10,11,12 CELL A2: 10 CELL A3: 11 CELL A4: 12 Any help would be greatly appreciated! /grod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
How do I insert an image into a specific cell within a protected . | Excel Discussion (Misc queries) | |||
automatically insert system time in a cell | Excel Discussion (Misc queries) | |||
Excel document properties insert into a cell | Excel Discussion (Misc queries) | |||
Can I insert digital signatures into an Excel cell? | Excel Discussion (Misc queries) |