EXCEL VBA:在每个单元格中的“点"之后设置文本格式 [英] EXCEL VBA: Format text after Point in each Cell

问题描述

在单元格中句子的最后一个点(.)之后，我有成千上万个带有引用(1至4个字符)的单元格.1-4参考字符的顺序不正确.

I have thousands of cells with a reference (1 to 4 characters) after the last Point ( . ) of the sentence inside the cell. the 1-4 reference character are not in a particular order.

例如在一个单元格中，我有:

For example in a cell I have:

|这是文本.L |

|This is a text. L|

或

|这是另一文本.IL |

|This is an other text. IL|

我手动选择了一系列细胞后，我想要一个VBA宏来选择该点之后的任何字符，如果单元格为空则不执行任何操作，然后选择将其设为粗体，红色和上标移至下一个单元格，然后再次执行直到结束

After I select a range of cellule manualy, I would like to have a VBA macro to Select after the point any character present, if cell empty do nothing and next Make it Bold, Red and superscript Move to the next cell and do again until the end

我尝试过

Sub BoldCellsLastWord()
lc = Right(ActiveCell, 1)
  With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
       Selection.Font.Superscript = True
       Selection.Font.Bold = True
  End With
End Sub

但是整个句子都被格式化了

but the whole sentence is getting formatted

我做错了什么?

在此先感谢您的帮助

推荐答案

此提议的解决方案:

• 与选择范围(根据需要更改)
• 验证 Cell 是否包含 Formula
• 验证 Cell 是否具有 Contents
• 使用 InStrRev 来确保 Cell 是否包含.，以确保其使用最后一个.
• 根据需要设置内容格式

• Works with the Selection range (change as required)
• Validates if the Cell contains a Formula
• Validates if the Cell has Contents
• Validates if the Cell contains a . using InStrRev to ensure it uses the last .
• Formats the contents as required

...

Sub Format_Text_In_Cells()
Dim Rng As Range, Cll As Range
Dim bLen As Byte, bPos As Byte

    Set Rng = Selection
    For Each Cll In Rng.Cells
        With Cll
            
            If Not (.HasFormula) Then           'Validates if cells contains a formula
            
                bLen = Len(.Value)                  'Get length of cell contents
                bPos = InStrRev(.Value, ".")        'Get position of last "."
                If bPos > 0 And bPos < bLen Then
                
                    'With .Characters(Start:=1 + bPos, Length:=bLen).Font   'Use this line to exclude the `.`
                    With .Characters(Start:=bPos, Length:=bLen).Font        'Use this line to include the `.`
                    
                        Rem Format Cell Contents
                        .FontStyle = "Bold"
                        .Superscript = True
                        .Color = XlRgbColor.rgbRed
    
    End With: End If: End If: End With: Next

    End Sub

单元格 [A5] 和 [A8] 包含公式.

这篇关于EXCEL VBA:在每个单元格中的“点"之后设置文本格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！