excel - 将句子中的特定单词加粗，但不突出显示“，Lead Bookrunner”之前的整个句子

Question

句子 1：优步，$9.37MM 股权。Stifel Nicolaus, Lead Bookrunner。（我想在“.”之后和“, Lead Bookrunner”之前加粗字符，以及“，”之前的起始字符，即银行名称和公司名称，在这种情况下是 Stifel Nicolaus & Uber。 “.”也出现在 9.37MM Sentence 2: Google, $750MM Credit deal 中。摩根大通，首席账簿管理人。

我的代码：

Set Where = Range("P2", Range("P" & Rows.Count).End(xlUp))
  With Where
    .Value = .Value
    .Font.Bold = False
  End With
  For Each This In Where
    i = InStr(This, ", Lead Bookrunner.")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = True
  Next
For Each This In Where
    i = InStr(This, ".")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = False
  Next
For Each This In Where
    i = InStr(This, ",")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = True
  Next

我通过我的代码得到的结果： 优步，9 美元。37MM 股权。Stifel Nicolaus，首席账簿管理人。得到我想要的结果：优步，$9.37MM 股权。Stifel Nicolaus，首席账簿管理人。

标签： excelvbacell

Answer 1

格式是否总是像 TEXT COMMA TEXT FULLSTOP TEXT COMMA TEXT(Lead Bookrunner)？EX 1. TEXT (Uber) COMMA TEXT($9.37MM Equity) FULLSTOP TEXT (Stifel Nicolaus) COMMA TEXT(Lead Bookrunner) EX 2. TEXT (Google) COMMA TEXT($750MM Credit deal) FULLSTOP TEXT (JP Morgan) COMMA TEXT( Lead Bookrunner) – Siddharth Rout 25 分钟前

是的，这种格式将保持不变（TEXT COMMA TEXT FULLSTOP TEXT COMMA Lead Bookrunner。） – ah Pl 3 分钟前

如果格式要保持这样，那么它真的很简单。

逻辑：

1. ,使用Instr查找左起第一个
2. ,使用InStrRev查找右起第一个
3. 查找.上面右边的第一个,使用InStrRev
4. 亮点

代码：

我已经评论了代码。如果您仍然遇到困难，请随时在下面发表评论。

这是你正在尝试的吗？

Option Explicit

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long
    Dim lPos As Long, rPosDot As Long, rPosComma As Long, strLen As Long
    Dim cellValue As String
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the range
        For i = 1 To lRow
            With .Range("A" & i)
                '~~> Remove bold formatting
                .Font.Bold = False
                
                cellValue = .Value2
                
                If InStr(1, cellValue, "(") Then
                    .Font.Bold = True
                ElseIf InStr(1, cellValue, "Lead Bookrunner", vbTextCompare) Then
                    '~~> Store the length of the cell value
                    strLen = Len(cellValue)
                    
                    '~~> Find the position of "," from left
                    lPos = InStr(1, cellValue, ",")
                    
                    '~~> Find the position of "," from right
                    rPosComma = InStrRev(cellValue, ",", -1, vbTextCompare)
                    
                    '~~> We need this so that we can find the position of "." on the
                    '~~> right of the ","
                    cellValue = Left(cellValue, rPosComma)
                    
                    '~~> Find the position of "." from the right of ","
                    rPosDot = InStrRev(cellValue, ".", -1, vbTextCompare)
                    
                    '~~> Bold the characters
                    .Characters(Start:=1, Length:=lPos - 1).Font.FontStyle = "Bold"
                    .Characters(Start:=rPosDot + 1, Length:=rPosComma - rPosDot - 1).Font.FontStyle = "Bold"
                End If
            End With
        Next i
    End With
End Sub

在行动：