VBA & Macro Problems

Count Names by Text and Bold Formatting in Excel with VBA

Steve KSteve K Nov 18, 2026 870 views

Question details

Count standalone names and names formatted in bold within groups in multiline Excel cells.

Count Names by Text and Bold Formatting in Excel with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Names appear on a specified line in each cell, either alone or as part of a group.
Observed behavior
COUNTIF can match text but cannot check the bold formatting of individual characters within a cell.
Before you start

Confirm that the name appears on the fifth line of each cell and that grouped names are separated by commas, semicolons, slashes, or ampersands. The VBA solution requires a desktop version of Excel that supports macros.

Solution 1Recommended

Create a VBA function to count qualifying names

Use a custom function when names must remain in multiline cells and character-level bold formatting determines the count.

The function checks the fifth line of each cell. It counts an exact standalone name regardless of formatting, or a matching group entry only if every character in that entry is bold. Each cell contributes at most one to the count.

1
Open the VBA editor

In Excel, press Alt+F11 to open the VBA editor, then select Insert > Module.

2
Add the function

Paste this code into the module: Function CountName(MyRange As Range, MyName As String) As Long Dim c As Range, lines() As String, ln As String, token As String Dim offset As Long, startPos As Long, leading As Long Dim i As Long, j As Long, k As Long, allBold As Boolean Application.Volatile If Len(Trim$(MyName)) = 0 Then Exit Function MyName = Trim$(MyName) For Each c In MyRange.Cells If Not IsError(c.Value2) Then lines = Split(CStr(c.Value2), vbLf) If UBound(lines) >= 4 Then ln = lines(4) If Right$(ln, 1) = vbCr Then ln = Left$(ln, Len(ln) - 1) If StrComp(Trim$(ln), MyName, vbTextCompare) = 0 Then CountName = CountName + 1 Else offset = 0 For j = 0 To 3 offset = offset + Len(lines(j)) + 1 Next j startPos = 1 For i = 1 To Len(ln) + 1 If i > Len(ln) Or InStr(1, ",;/&", Mid$(ln, i, 1), vbBinaryCompare) > 0 Then token = Mid$(ln, startPos, i - startPos) leading = Len(token) - Len(LTrim$(token)) token = Trim$(token) If StrComp(token, MyName, vbTextCompare) = 0 Then allBold = True For k = 0 To Len(token) - 1 If Not c.Characters(offset + startPos + leading + k, 1).Font.Bold Then allBold = False Exit For End If Next k If allBold Then CountName = CountName + 1 Exit For End If End If startPos = i + 1 End If Next i End If End If End If Next c End Function

3
Enter the formula

Return to the worksheet and enter =CountName(A3:M3,"Gold") in an empty cell. Change the range and name to match your data.

4
Save and allow the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). When reopening a trusted workbook, use Enable Content if Excel prompts you to allow macros.

Create a VBA function to count qualifying names
Recalculate after formatting changes: Changing bold formatting alone may not trigger recalculation. Press F9 to recalculate the formula after changing formatting. The function does not count partial names or groups separated only by spaces.
Free Microsoft Office alternative

Organize and count names in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open a compatible Excel workbook and organize names and status labels in columns for formula-based counting; it does not fix Excel-specific VBA issues.

Count names by explicit status using spreadsheet formulasCompatible with Microsoft Excel XLSX and XLS formatsFamiliar interface helps make migration from Office seamlessFree and lightweight for everyday spreadsheet work
microsoft office alternative - wps office

Frequently Asked Questions

Can COUNTIF detect bold text in part of a cell?

No. COUNTIF evaluates cell values, not character-level formatting. Use the VBA function with Characters.Font.Bold or record the formatting status in a separate column.

Why does the function check only the fifth line?

The example assumes the name is on line five and reads lines(4), because the split array starts at zero. Change that index and the corresponding offset loop if your names are on another line.

Why didn't the count change after I made a name bold?

Formatting changes may not trigger formula recalculation. Press F9 in Excel to recalculate after changing the bold formatting.

Does the function count a name embedded inside another name?

No. It compares complete entries separated by commas, semicolons, slashes, or ampersands. For example, Gold does not match Golden.

What file format preserves the VBA function?

Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Enable macros when prompted only if you trust the workbook.