|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extracting multiple words from one cell into individual rows while copying all other data
Hi,
I am trying to accomplish the below and wondered if anyone may be able to help out as I have been trying to get a solution for few days but haven't found a good one yet. I have a table with hundreds of lines like the below: ID Date Name 1 1/1/15 Joe<br>Mary<br>Steph ... and I'd like to get it to convert to ID Date Name 1 1/1/15 Joe 1 1/1/15 Mary 1 1/1/15 Steph Thanks for any suggestions! Props to macropod for teaching me how to add a file Randy Last edited by randyaserve; 10-01-2015 at 06:26 AM. Reason: Adding Excel file (thanks macropod!) |
#2
|
||||
|
||||
What you're asking for would require a macro. However, it's not really clear from your post how the data appear in your workbook. Perhaps you could attach one with some sample data to a post. You can do that via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Paul, I added the sample file attached. Thanks for the heads up!
Randy |
#4
|
||||
|
||||
Try:
Code:
Sub Demo() Application.ScreenUpdating = False Dim i As Long, j As Long, k As Long With ThisWorkbook.Worksheets("Sheet1").UsedRange For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1 If InStr(.Cells(i, 5).Value, "<br>") > 0 Then For j = UBound(Split(.Cells(i, 5).Value, "<br>")) To 1 Step -1 .Cells(i + 1, 5).EntireRow.Insert Shift:=xlShiftDown For k = 1 To 4 .Cells(i + 1, k).Value = .Cells(i, k).Value Next .Cells(i + 1, 5).Value = Split(.Cells(i, 5).Value, "<br>")(j) Next .Cells(i, 5).Value = Split(.Cells(i, 5).Value, "<br>")(0) End If Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi, this seems to work well! I appreciate the help macropod. Cheers mate!
Quote:
|
Tags |
data, macros |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extracting data from a cell with multiple lines of text | MMT | Excel | 9 | 02-12-2015 09:18 PM |
copying nested if over multiple rows where one value stays fixed | charles_cat | Excel | 1 | 01-23-2015 01:30 AM |
Copying data from one cell to another where condition is third cell | fairyca | Excel | 4 | 03-30-2014 08:22 AM |
Grouping table rows to prevent individual rows from breaking across pages | dennist77 | Word | 1 | 10-29-2013 11:39 PM |
Copying data from one cell to another automatically | mrphilk | Excel | 4 | 06-10-2010 11:52 PM |