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 received an Excel document that has data that looks like the following:

    A               B           C
  =========================================
1 |F:\folderName  |            |          |
2 |AAA            |            |          |
3 |  AAA1         | None       |          |
4 |  AAA2         |            |          |
5 |    Somedoc.doc|            |          |
6 |F:\folderName2 |            |          |
7 |BBB            |            |          |
8 |  BBB1         |            |          |
9 |    doc2.doc   |            |          |
10|  BBB2         | None       |          |
... continues ...

The folders can be nested to any depth and the indentations are from using "Format Cells" > "Alignment" > "Indent". Cells with no documents underneath or the word "None" in the second column can be ignored. I need to convert the above list to something usable like:

F:\folderName\AAA\AAA2\Somedoc.doc
F:\folderName2\BBB\BBB1\doc2.doc

How can I do this? If I could modify the cells to replace the indentation format with some real spaces I could probably copy the content to a text file and use regular expressions to put the file names together. Is that possible?

share|improve this question
    
Do you want a VBA or worksheet-function solution? –  Lance Roberts Nov 10 '10 at 21:42
    
Whatever gets the job done. This is just a one-time thing. –  adam0101 Nov 10 '10 at 21:54

1 Answer 1

up vote 2 down vote accepted

In VBA:

Sub FileNameFix()

Dim FullRange As Range
Dim CopyIndex As Integer, PasteIndex As Integer
Dim RowIndex As Integer, LastRow As Integer
Dim i As Integer
Dim Text(0 To 10) As String
Dim None(0 To 10) As String

LastRow = Range("A65536").End(xlUp).Row
Set FullRange = Range("A1:B" & LastRow)
CopyIndex = 1
PasteIndex = 1

While CopyIndex < LastRow

    For i = 0 To 10
        Text(i) = ""
    Next i

    Do
        Text(0) = FullRange.Cells(CopyIndex, 1)
        CopyIndex = CopyIndex + 1
    Loop Until Mid(Text(0), 2, 1) = ":"
    RowIndex = 1
    Do
        Text(RowIndex) = FullRange.Cells(CopyIndex, 1)
        None(RowIndex) = FullRange.Cells(CopyIndex, 2)
        CopyIndex = CopyIndex + 1
        RowIndex = RowIndex + 1
    Loop Until Right(Text(RowIndex - 1), 3) = "doc"

    For i = 1 To RowIndex - 1
        If None(i) <> "None" Then
            Text(0) = Text(0) + "\" + Text(i)
        End If
    Next i

    Sheets("Sheet2").Cells(PasteIndex, 1) = Text(0)
    PasteIndex = PasteIndex + 1

Wend

End Sub
share|improve this answer

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.