Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-30-2015, 01:19 PM
randyaserve randyaserve is offline Extracting multiple words from one cell into individual rows while copying all other data Windows 7 64bit Extracting multiple words from one cell into individual rows while copying all other data Office 2010 64bit
Novice
Extracting multiple words from one cell into individual rows while copying all other data
 
Join Date: Sep 2015
Posts: 3
randyaserve is on a distinguished road
Default 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
Attached Files
File Type: xlsx Excel Example.xlsx (9.4 KB, 10 views)

Last edited by randyaserve; 10-01-2015 at 06:26 AM. Reason: Adding Excel file (thanks macropod!)
Reply With Quote
  #2  
Old 09-30-2015, 11:38 PM
macropod's Avatar
macropod macropod is online now Extracting multiple words from one cell into individual rows while copying all other data Windows 7 64bit Extracting multiple words from one cell into individual rows while copying all other data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 10-01-2015, 06:27 AM
randyaserve randyaserve is offline Extracting multiple words from one cell into individual rows while copying all other data Windows 7 64bit Extracting multiple words from one cell into individual rows while copying all other data Office 2010 64bit
Novice
Extracting multiple words from one cell into individual rows while copying all other data
 
Join Date: Sep 2015
Posts: 3
randyaserve is on a distinguished road
Default

Paul, I added the sample file attached. Thanks for the heads up!

Randy
Reply With Quote
  #4  
Old 10-01-2015, 02:16 PM
macropod's Avatar
macropod macropod is online now Extracting multiple words from one cell into individual rows while copying all other data Windows 7 64bit Extracting multiple words from one cell into individual rows while copying all other data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 10-05-2015, 09:52 AM
randyaserve randyaserve is offline Extracting multiple words from one cell into individual rows while copying all other data Windows 7 64bit Extracting multiple words from one cell into individual rows while copying all other data Office 2010 64bit
Novice
Extracting multiple words from one cell into individual rows while copying all other data
 
Join Date: Sep 2015
Posts: 3
randyaserve is on a distinguished road
Default

Hi, this seems to work well! I appreciate the help macropod. Cheers mate!

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
Reply

Tags
data, macros

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting multiple words from one cell into individual rows while copying all other data 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
Extracting multiple words from one cell into individual rows while copying all other data Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Extracting multiple words from one cell into individual rows while copying all other data Copying data from one cell to another automatically mrphilk Excel 4 06-10-2010 11:52 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft