Excel Data Parsing and Structuring Question

Posted on 2011-05-10
Last Modified: 2012-05-11
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?

Question by:tmonteit
    1 Comment
    LVL 65

    Accepted Solution

    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.


    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
        MsgBox "Finished"
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Read about achieving the basic levels of HRIS security in the workplace.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now