Replace cell formatting with text in Excel?

Question

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?

Answer 1

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