Excel Data Parsing and Structuring Question

I need help with a macro or an Excel function that can add some structure a poorly formatted Excel spreadsheet.  The spreadsheet is an activity tracker and the author didn't understand that they were supposed to only put 1 activity on a single row.  Instead they put multiple activities into a single cell.  They also combined seperate types of data such as dates and types into a single cell.   This is frustrating because the data is useless in its current form.

   1.  How do I build multiple rows of data based on a single multi-line cell?

   2.  For each of these new rows how do I parse out a date from an Activty type? The author combined these.  

There are about 1000 lines of data.  So if I have to format this thing manually, it will be a killer. The attached spreadsheet has 2 tabs (Given, and Needed)

Is there an easy way to accomplish this with a VBA script or excel funtion?  Examples?


EE-parsing-quest-v2x.xls
tmonteitAsked:
Who is Participating?
 
RobSampsonCommented:
Hi there,

Create a sheet called SEPARATED and add the first row header names to it.

Then, with your data on the sheet called GIVEN, run this macro.  Once finished, have a look at the SEPARATED sheet.

Regards,

Rob.
Sub SeparateData()
    Set objGiven = Sheets("GIVEN")
    Set objSep = Sheets("SEPARATED")
    For intRow = 2 To objGiven.Cells(65536, "A").End(xlUp).Row
        strID = objGiven.Cells(intRow, "A").Value
        strType = objGiven.Cells(intRow, "B").Value
        For Each strLine In Split(objGiven.Cells(intRow, "C").Value, vbLf)
            strDate = Left(strLine, InStr(strLine, " "))
            strActivity = Trim(Mid(strLine, InStr(strLine, " ") + 1))
            intNextRow = objSep.Cells(65536, "A").End(xlUp).Row + 1
            objSep.Cells(intNextRow, "A").Value = strID
            objSep.Cells(intNextRow, "B").Value = strType
            objSep.Cells(intNextRow, "C").Value = strDate
            objSep.Cells(intNextRow, "D").Value = strActivity
        Next
    Next
    MsgBox "Finished"
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.