Extract items from 1 huge cell in Excel

Question

I have a huge spreadsheet of PC's and the software that is installed on them.
Unfortunately the report lists all the software installed for a PC in one cell.

I need a formula or macro which will go through every piece of software in the cell and extract all software that contains the word Microsoft and put it in a new cell in the next column on the same row. It must do this for every item found so that each Microsoft product installed appears in separate cells on the 1 row.

I have tried various things but am not sure how to get it to output every instance. I'm assuming it needs some sort of array and loop but wouldn't have a clue how to do it.

If any further info is required, please let me know.
Your help would be greatly appreciated.

The software cell looks like this:

"Adobe Flash Player 10 Plugin | 10.3.181.26"; "Adobe Flash Player 11 ActiveX 64-bit | 11.0.1.152"; "Adobe Reader 9.4.6 | 9.4.6"; "Altiris Deployment Agent | 1.0.0"; "BigFix Enterprise Client | 7.2.5.22"; "Citrix Access Gateway Plugin | 4.6.1.2700"; "Citrix Password Manager Plugin | 4.6.264.0"; "Citrix XenApp Web Plugin | 11.0.0.5357"; "Definition update for Microsoft Office 2010 (KB982726)"; "Intel(R) Graphics Media Accelerator Driver"; "JXplorer | 3.2.2"; "Java(TM) 6 Update 16 | 6.0.160"; "McAfee Agent | 4.5.0.1852"; "McAfee AntiSpyware Enterprise Module | 8.7.0.129"; "McAfee Encrypted USB Client | 1.5.0"; "McAfee VirusScan Enterprise | 8.7.0"; "Microsoft .NET Framework 4 Client Profile | 4.0.30319"; "Microsoft Application Virtualization Desktop Client | 4.6.0.1523"; "Microsoft Conferencing Add-in for Microsoft Office Outlook | 8.0.6362.187"; "Microsoft Office Communicator 2007 | 2.0.6362.189"; "Microsoft Office InfoPath MUI (English) 2010 | 14.0.4763.1000"; "Microsoft Office Live Meeting 2007 | 8.0.6362.187"; "Microsoft Office Professional Plus 2010 | 14.0.4763.1000"; "Microsoft Office Visio 2007 Service Pack 2 (SP2)"; "Microsoft Office Visio Professional 2007 | 12.0.6425.1000"; "Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office programs | 12.0.4518.1014"; "Microsoft Silverlight | 4.0.60831.0"; "Microsoft Sync Framework Runtime v1.0 (x86) | 1.0.1215.0"; "Microsoft Sync Framework Services v1.0 (x86) | 1.0.1215.0"; "Microsoft Visual C++ 2005 Redistributable - KB2467175 | 8.0.51011"; "Microsoft

Answer 1

This should do the job, just replace the worksheet and the range:

Sub ExtractSoftware()
    Dim ws as Worksheet    
    Dim lngRow As Long
    Dim rngSource As Range
    Dim rng As Range
    Dim varElement As Variant

    Set ws = Sheets("YourSheet")
    For lngRow = 1 To ws.Range("A1000000").End(xlUp).Row
        Set rngSource = ws.Cells(lngRow, 1)
        Set rng = rngSource.Offset(, 1)

        For Each varElement In Split(rngSource.Value, ";")
            If InStr(varElement, "Microsoft") Then
                varElement = Trim(varElement)
                rng.Value = Mid(varElement, 2, Len(varElement) - 2)
                Set rng = rng.Offset(, 1)
            End If
        Next varElement
    Next lngRow

End Sub

Answer 2

I'd use a combination of Find and Replace, and Text to Columns.

1. Copy the column in question to the end of the row. We're going to replace some of the data, so doing this in a 2nd column avoids losing your data if you make a mistake. (Belt and braces!)

2. Use the Find and Replace function to replace all instances of Microsoft with two special characters that won't be found elsewhere in this column. I often use ¬ and ` as they are rarely used, so you're replacing Microsoft with ¬`.

3. Now use the Text to Columns function on the data tab. Set your delimiter as your first special character: ¬. This should now give you cells prefixed with Microsoft items. However, you also have a lot of other stuff following it.

4. To remove this extraneous stuff, select all the columns containing this info and do another Find and Replace. Find this: ;* and replace with nothing (keep the textbox empty). This should strip out anything non-Microsoft that you don't want.

5. Finally you can re-add your label Microsoft by doing a Find and Replace on ` and changing it back to Microsoft.

6. You may have some outstanding trailing " signs. Use another Find and Replace to remove them.

Once you get used to hacking info around like this you can do it very quickly, believe me!