Count Names by Text and Bold Formatting in Excel with VBA
Question details
Count standalone names and names formatted in bold within groups in multiline Excel cells.

- 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.
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.
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.
In Excel, press Alt+F11 to open the VBA editor, then select Insert > Module.
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
Return to the worksheet and enter =CountName(A3:M3,"Gold") in an empty cell. Change the range and name to match your data.
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.

Store names and their status in separate columns
Use normalized data for a formula-based count that does not rely on character formatting or VBA.
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.

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.




