[Last Call] Learn how to a build a cloud-first strategyRegister Now


Excel Data Parsing and Structuring Question

Posted on 2011-05-10
Medium Priority
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

RobSampson earned 2000 total points
ID: 35735147
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


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

825 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