如何在Concatenate公式中为指定文字设置自动加粗格式？

Great question! Let's cut to the chase: Excel's native CONCATENATE (or its more versatile cousin TEXTJOIN) can't spit out formatted text directly—since these functions only return plain text strings, no formatting data attached. But you don't have to manually format every time; here are three solid workarounds tailored to different scenarios:

1. Custom VBA Function (Most Flexible for Dynamic Formatting) #

If you need to dynamically bold specific words/segments (even consecutive ones), a VBA custom function is your best bet. It lets you build concatenated text while applying formatting to exactly the parts you want. Here's a ready-to-use example:

Function ConcatWithBold(targetCell As Range, ParamArray textSegments() As Variant) As Boolean
    ' Clear existing content/formatting in the target cell first
    targetCell.Clear
    
    Dim currentPosition As Integer
    currentPosition = 0
    
    ' Loop through each text segment to build and format the result
    For i = LBound(textSegments) To UBound(textSegments)
        Dim segText As String
        Dim makeBold As Boolean
        segText = textSegments(i)(0)
        makeBold = textSegments(i)(1)
        
        ' Add the text to the target cell
        targetCell.Value = targetCell.Value & segText
        
        ' Apply bold formatting to this segment
        targetCell.Characters(currentPosition + 1, Len(segText)).Font.Bold = makeBold
        
        ' Update position for the next segment
        currentPosition = currentPosition + Len(segText)
    Next i
    
    ConcatWithBold = True
End Function

To use this:

• Press Alt + F11 to open the VBA Editor
• Insert a new module (Insert > Module)
• Paste the code above
• Go back to your worksheet, then use it in a cell like this (example: combine A1, bold B1:C1's content, then add D1):
  =ConcatWithBold(E1, (A1, FALSE), (B1&C1, TRUE), (D1, FALSE))
• Hit enter, and cell E1 will have your formatted concatenated text.

This works for any number of bold segments—just group your target consecutive words into a single (text, TRUE) parameter.

2. Conditional Formatting (For Fixed, Repeating Keywords) #

If you're always bolding the exact same words (no dynamic changes), conditional formatting can automate this:

• Select the cell(s) with your CONCATENATE result
• Go to Home > Conditional Formatting > New Rule
• Choose Use a formula to determine which cells to format
• Enter a formula like =ISNUMBER(SEARCH("your bold word", A1)) (replace A1 with your target cell)
• Click Format > Font > Bold > OK
• Repeat this rule for every additional word you want bolded.

Note: This only works for fixed keywords—if you need to switch which words are bolded based on other cells, stick with VBA.

3. Semi-Automated Word Workaround (No Coding Needed) #

If you don't want to mess with VBA, you can use Word's find-and-replace to speed up formatting:

• Copy your plain-text concatenated result from Excel
• Paste it into a Word document
• Press Ctrl + H to open Find and Replace
• In the Find what box, enter the word(s) you want bolded
• Click More > Format > Font > Bold > OK
• Leave Replace with blank (the formatting will apply to the found text)
• Click Replace All, then copy the formatted text back to Excel.

This is way faster than manual formatting, even if it's not fully automated.

内容的提问来源于stack exchange，提问作者Noctis