Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
By the way, I want to output the data into the cell looking like this: Microsoft Office Professional Plus 2010 | 14.0.4763.1000 –  Michael Jan 21 '13 at 2:17
    
What is the number of software items in the cell, would you say? Enough to fit within the total number of columns in Excel if the cell was broken apart? –  chuff Jan 21 '13 at 4:31
    
I'd forgotten that Excel 2010 has over 16,000 columns. What you want can be done easily using the built-in commands on the Ribbon, but I take it you'd prefer an automatic solution? –  chuff Jan 21 '13 at 4:42
    
Hi thanks for the reply. I would say there is probably about 50 items each PC would installed so definately enough columns hehe. Yes I need an automatic solution as its for thousands of PC's. –  Michael Jan 21 '13 at 5:02
add comment

2 Answers

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
share|improve this answer
    
This is a good start, but returns all items, not just Microsoft items. –  Andi Mohr Jan 21 '13 at 10:55
1  
Oh, oversaw the filtering for MS. I changed the answer. –  Peter Albert Jan 21 '13 at 11:23
    
I think your original comment about text to columns combined with a find formula is a more flexible solution. –  Pynner Jan 21 '13 at 11:25
    
@Pynner: As Ani Mohr already provided the full solution to this approach, I wanted to provide the VBA approach, which does the job faster - and can also be flexible (but then requiring some changes in the code) –  Peter Albert Jan 21 '13 at 11:53
1  
@Michael: There were two references to the worksheet in the code - I modified it, so you now only need to change the name of your sheet in the first Set statement and the range. Then it should hopefully work. –  Peter Albert Jan 22 '13 at 9:04
show 4 more comments

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!

share|improve this answer
    
Though in most cases, you probably need to delete the first column... –  Peter Albert Jan 21 '13 at 12:55
    
Yep, quite often! –  Andi Mohr Jan 21 '13 at 14:31
    
Sorry mate ive been very busy and didnt have a chance to check for answers. I will give this a try and see how we go. Your effors are greatly appreciated, cant believe there are stll so many nice ppl around. –  Michael Jan 22 '13 at 4:09
    
This is a very good solution, thank you. I will use this one for now but the VBA solution will also be useful for when I need to do this for hundreds of other reports for different products. Thanks again –  Michael Jan 22 '13 at 4:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.