Excel users often find themselves finding specific format entries. Formula to find bold text in Excel is one such incident. Among hundreds of entries, finding bold entries or texts is an easy but tricky job. Excel features, GET.CELL formula as well as VBA macro can find bold texts.
Let’s say we have a dataset that holds products sold on certain days. The products that have sold Quantity of more than 50 (>50) are in bold. We have to find those bolden product names.
Download Excel Workbook
4 Uses of Formula to Find Bold Text in Excel
Method 1: Using Find Feature to Find Bold Text in Excel
Excel Find feature finds formatted entries such as specific text, character, or format. Selecting Bold as a search Format enables the Find feature to find bold texts within a worksheet or selection.
Step 1: Select the entire range (can be rows or columns) then, Go to the Home tab > Choose Find & Select (from Editing section) > Select Find.
Step 2: The Find and Replace dialog box appears. From the dialog box,
Keep other options as it is.
Just Click on Format.
Step 3: In a moment, the Find Format dialog box appears. In the dialog box,
Select the Font section.
Go to the group Font Style.
Select Bold and Click OK.
Step 4: Clicking OK takes you to the Find and Replace dialog box. Click on Find All. All the bold texts in the selection get stacked under the dialog box as shown in the following picture.
Step 5: Press CTRL+A to select all the bold texts altogether. You see Excel highlights all the bold texts within our selection.
➤ You can see the Find feature gray shaded all the bold texts within the selection. You can modify the selected range to find bold texts. In case of not assigning any selection, Find finds bold entries within the entire worksheet.
Read More: How to Find Text in Cell in Excel
Method 2: Find Bold Text Using GET.CELL Formula
GET.CELL is a macro function that returns TRUE or FALSE depending on the types of cell information. The syntax of the GET.CELL is
In the syntax,
type_num; is a number that indicates what type of cell info you consider finding. For this case type_num 20 refers to the first or all the characters of an entry in Bold font.
reference; cell reference.
Step 1: Highlight the entire column. Afterward, go to the Formulas tab > Select Define Name (from the Defined Names section).
Step 2: The New Name dialog box appears. Assign the range (i.e., Product column) a name (i.e., FindBold). Then, assign the following formula to the Refer to a dialog box.
In the formula,
20 is the type_num that indicates entries with Bold font or characters. GET.CELL!$C5 is the reference. The formula is set to return cell information (i.e., which cell is in Bold) in TRUE or FALSE.
Step 3: Now, back to the worksheet. Type
=FindBold in any blank cell (i.e., G5), a defined name appears under the formula bar as shown in the below screenshot. Double click on it.
Step 4: Hit ENTER and Drag the Fill Handle to apply the formula to other cells. Doing so Excel displays TRUE for bold entries and FALSE for otherwise.
Step 5: Place the cursor on the header (i.e., Find Bold Text). After that, go to Home > Select Sort & Filter (from the Editing section) > Click on Filter.
Step 6: After applying Filter, Click on the Filter icon, the Filter options appear. Deselect FALSE, then, click OK.
➤ Executing Filter displays all the bolden entries and hides other non-bolden entries.
- Excel Function: FIND vs SEARCH (A Comparative Analysis)
- How to Find External Links in Conditional Formatting in Excel (2 Ways)
- How to Find Max Value in Range with Excel Formula (5 Easy Methods)
- FIND Function Not Working in Excel (4 Reasons with Solutions)
- [Solved!] CTRL+F Not Working in Excel (5 Fixes)
Method 3: Using VBA Custom Formula to Find Bold Text in Excel
Excel VBA is an efficient tool to achieve any criteria-based outcomes. VBA can generate a custom function to find or extract bold texts from given ranges.
Select Insert (from the Toolbar) > Click on Module (from the options).
Step 2: Paste the following macro in the Module.
Function FindBold(wrkRng As Range) If wrkRng.Font.Bold Then FindBold = wrkRng.Value Else FindBold = "" End If End Function
The macro declares the custom function name as FindBoldText and takes an argument as a range. The VBA IF function imposes a condition to insert blanks in case of non-bolden entries otherwise fetches them.
Step 3: Back to the worksheet, try to type
=FindBoldText in the formula bar. A custom formula of the same name as in the macro appears similar to the picture below. Double click on it.
Step 4: Assign a cell as a reference and Hit ENTER.
Step 5: Use the Fill Handle to apply the formula to the rest of the cells.
Step 6: Repeat Steps 5 and 6 of Method 2 to apply Filter and display Filter options. Unselect Blanks from the Filter options then click OK.
➤ Excel hides all the non-bolden entries within the range and displays just the bolden entries as depicted in the picture below.
Method 4: Find Bold Text Using VBA Macro
A simple macro can find bold text within a selection.
Step 1: Select a range then Press ALT+F11 to open Microsoft Visual Basic window. Use Step 1 of Method 3 to insert a Module in the window.
Step 2: Type the below macro in the Module.
Sub FindBoldEntries() Dim mrfRng As Range Dim wrkRng As Range Dim LRng As Range On Error Resume Next xTitleId = "Provide a Range" Set wrkRng = Application.Selection Set wrkRng = Application.InputBox("Range", xTitleId, wrkRng.Address, Type:=8) For Each mrfRng In wrkRng If mrfRng.Font.Bold Then If LRng Is Nothing Then Set LRng = mrfRng Else Set LRng = Union(LRng, mrfRng) End If End If Next If Not LRng Is Nothing Then LRng.Select End If End Sub
In the macro, VBA FOR creates a loop to clear out non-bolden entries using the VBA IF statement. Cells that satisfy VBA IF get assigned as LRng and macro highlight them in the end.
Step 3: Use the F5 key to run the macro. As we select the range earlier, Excel automatically inserts the selection in Provide a Range dialog box. Click OK.
Step 4: All the bold entries get highlighted as you can see in the latter screenshot.
You can assign multiple ranges to find bold texts. This macro can also find bolden values within a given range.
In this article, we use features and formulas to find bold text in Excel. Excel Find feature, GET.CELL formula finds bold text with ease. Also, VBA custom formula and macro do the job efficiently. Hope these above-mentioned methods serve you well fulfilling your requirements. Comment if you have further inquiries or have anything to add.