Extract bold text from cells in Excel

Sometimes, when copying text from external sources, you may only need to extract the words that are in bold. Excel’s standard functions don’t provide a way to filter or extract text based on font style.

Extracting Bold Text with VBA

If you don’t have !SEMTools installed, you can use VBA UDF that loops through the characters in a cell and collects only the bold text:

Function ExtractBoldText(rng As Range) As String
    Dim i As Long
    For i = 1 To Len(rng.Value)
        If rng.Characters(i, 1).Font.Bold Then
            ExtractBoldText = ExtractBoldText & Mid(rng.Value, i, 1)
        End If
    Next i
End Function

!SEMTools for Excel includes a ready-to-use tool for extracting bold characters directly from your worksheets — no coding required. You can find this feature in the Extract Symbols menu:

As demonstrated in the animation, the tool extracts bold text using a sophisticated — not primitive —method:

• Consecutive bold words are kept together as a single phrase. When separated by non-bold text, a vertical bar “|” is inserted as a delimiter. This prevents all the bold terms from being mashed into one long string and provides a clean separator for further processing.

• It preserves original spaces between characters, so extracted words remain legible.

Summing Values in Bold Cells

You may also need to sum only the numbers that are formatted in bold. Excel doesn’t offer a built-in filter for font weight. But, if you use !SEMTools, the same result can be achieved instantly with Find Bold characters macro in Find characters menu. It will mark with TRUE and FALSE cells if they contain or don’t contain bold characters, respectively. After that you can use SUBTOTAL function to sum only cells filtered by TRUE value.

This post is also available in RU.