☰

在 Excel 中用文本替换单元格格式？

tag-icon • tag-icon microsoft-excel vba string-manipulation
在 Excel 中用文本替换单元格格式？

我收到一份 Excel 文档，其中包含如下数据：

    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 ...

文件夹可以嵌套到任意深度，缩进来自使用“设置单元格格式”>“对齐”>“缩进”。可以忽略下面没有文档的单元格或第二列中的单词“无”。我需要将上面的列表转换为可用的内容，例如：

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

我该怎么做？如果我可以修改单元格以用一些实际空格替换缩进格式，我可能会将内容复制到文本文件中并使用正则表达式将文件名放在一起。这可能吗？

答案1

在 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

相关内容