时间戳

首页 > 解决方案 > 在 MS Excel 中查找和替换下标和上标

html - 在 MS Excel 中查找和替换下标和上标

问题描述

我想在 excel 单元格中找到所有包含下标和上标的数字和公式，并将其替换为下标和上标的 html 标签。

例如。包含2 + (b 3 - c)的单元格将被替换为：

a<sup>2</sup> + (b<sup>3</sup> - c)

非常感谢。

标签： htmlexcel

解决方案

据我尝试，那个总是有效，但在第一个字符上失败。因此，如果第一个字符是下标或上标，它将失败。

它用于上标情况的代码。对于下标的情况，只需更改html 上的任何代码（.Font.Subscript上标上）。.Font.Superscript<sup>

Sub test()
Dim ColNo, RowNo As Long
Dim Pos(500) As Integer
Dim Str(500) As String
Dim sType(500) as String
Dim NewStr As String

Set ws1 = Worksheets("Hoja1")
Set ws2 = Worksheets("Hoja2")
ws1.Activate

With ws1
    RowNo = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
    ColNo = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
    l = 2
    For i = 1 To ColNo
        For j = 1 To RowNo
            Pos(1) = 1
            For k = 1 To Len(.Cells(j, i).Value2) - 1
                If .Cells(j, i).Characters(k + 1, 1).Font.Superscript = True Then
                    Pos(l) = k + 1
                    sType(l) = "Sup"
                    l = l + 1
                ElseIf .Cells(j, i).Characters(k + 1, 1).Font.Subscript = True Then
                    Pos(l) = k + 1
                    sType(l) = "Sub"
                    l = l + 1
                End If
            Next
            For k = 1 To l - 1
                If Pos(k + 1) > Pos(k) Then
                    If sType(l + 1) = "Sup" Then
                        Str(2 * k) = Mid(.Cells(j, i).Value2, Pos(k), Pos(k + 1) - Pos(k))
                        Str(2 * k - 1) = Mid(.Cells(j, i).Value2, Pos(k + 1), 1)
                        Str(2 * k - 1) = "<sup>" & Str(2 * k - 1) & "</sup>"
                        NewStr = NewStr & Str(2 * k) & Str(2 * k - 1)
                    ElseIf sType(l + 1) = "Sub" Then
                        Str(2 * k) = Mid(.Cells(j, i).Value2, Pos(k), Pos(k + 1) - Pos(k))
                        Str(2 * k - 1) = Mid(.Cells(j, i).Value2, Pos(k + 1), 1)
                        Str(2 * k - 1) = "<sub>" & Str(2 * k - 1) & "</sub>"
                        NewStr = NewStr & Str(2 * k) & Str(2 * k - 1)
                    End If
                End If
            Next
            If NewStr <> "" Then
                NewStr = NewStr + Mid(.Cells(j, i).Value2, _
                                  Pos(l - 1), Len(.Cells(j, i).Value2) - Pos(l - 1))
            Else
                NewStr = .Cells(j, i).Value2
            End If
            ws2.Cells(j, i).Value2 = NewStr
            NewStr = ""
            For k = 1 To l - 1
                Pos(k) = 0
                sType(k) = ""
                Str(2 * k) = ""
                Str(2 * k - 1) = ""
            Next
            l = 2
        Next
    Next
End With

End Sub

希望能帮助到你

推荐阅读