主页 / computer / 问题 / 1584990
Accepted
lanselibai
Asked: 2020-09-13 14:33:13 +0800 CST

如何使用函数在 Excel 中定义下标/上标？

  • 772

我想格式化 Excel 中文本的下标。由于我有很多文本，我想批量进行。请问有没有把文字转为下标的功能？

我想使用功能，而不是热键。

例如，将VL转换为V L。

microsoft-excel worksheet-function

1 个回答

  1. Best Answer

    为此，您可以编写一个宏，然后从开发人员选项卡中运行它。
    有关如何运行宏或启用开发人员工具的更多信息，请参阅支持页面

    'You can make the first character in the A1 cell subscript like this
Worksheets("Sheet1").Range("A1").Characters(0, 1).Font.Subscript = True

    扩展示例

    Sub SubscriptWithRegex()
    'Create a new regex object, import reference from
    'Tools > References > Microsoft VBScript Regular Expressions 5.5
    Set regex = New RegExp
    'Define the pattern, use regexr.com to test and write regex
    'For this example we will subscript all L characters after a V
    regex.Pattern = "V(L+)"
    'if TRUE, find all matches, if FALSE find only the first match
    regex.Global = True
    'Iterate trough cells in Sheet1 range A1:A6
    For Each cell In Worksheets("Sheet1").Range("A1:A6")
        'Search for the pattern
        Set matches = regex.Execute(cell.Value)
        'Iterate trough the matches
        For Each Match In matches
            'Make characters subscript
            'We need to shift the match to left by 2 to skip the V character
            'and we need to subtract that single V from the total length
            cell.Characters(Match.FirstIndex + 2, Match.Length - 1).Font.Subscript = True
        Next
    Next
End Sub

    运行宏之前和之后

    +----------+----------+
| 之前 | 之后 |
+----+------+----+------+
| | 一个 | | 一个 |
+----+------+----+------+
| 1 | 超大容量 | 1 | V LLL |
+----+------+----+------+
| 2 | VL | 2 | 大号   | _
+----+------+----+------+
| 3 | VVLL | 3 | VV LL |
+----+------+----+------+
| 4 | VLVL | 4 | V L V L |
+----+------+----+------+
| 5 | LLL | 5 | LLL |
+----+------+----+------+
| 6 | 五 | 6 | 五 |
+----+------+----+------+

    参考
    Excel VBA 参考
    字体对象 (Excel)
    字符对象 (Excel)

    • 0

相关问题