How to Use Formula to Find Bold Text in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset-Formula to Find Bold Text in Excel


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.

Find feature-Formula to Find Bold Text in Excel

Step 2: The Find and Replace dialog box appears. From the dialog box,

Keep other options as it is.

Just Click on Format.

Find and replace window

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.

Find format window

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.

Find and replace

Step 5: Press CTRL+A to select all the bold texts altogether. You see Excel highlights all the bold texts within our selection.

Find and replace

➤ 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.

Find feature

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

GET.CELL(type_num, reference)

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).

Get cell-Formula to Find Bold Text in Excel

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.

=GET.CELL(20,GET.CELL!$C5)

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.

new name window

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.

Inserting formula-Formula to Find Bold Text in Excel

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.

result

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.

Selecting Filter-Formula to Find Bold Text in Excel

Step 6: After applying Filter, Click on the Filter icon, the Filter options appear. Deselect FALSE, then, click OK.

Unselect False

➤ Executing Filter displays all the bolden entries and hides other non-bolden entries.

Final result

Read More: How to Get Excel Sheet Name


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.

Step 1: Hit ALT+F11 altogether. Microsoft Visual Basic window appears. In the window,

Select Insert (from the Toolbar) > Click on Module (from the options).

insert module-Formula to Find Bold Text in Excel

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

macro

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.

Custom formula insertion

 Step 4: Assign a cell as a reference and Hit ENTER.

Formula

Step 5: Use the Fill Handle to apply the formula to the rest of the cells.

Fill handle

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.

Filter

➤ Excel hides all the non-bolden entries within the range and displays just the bolden entries as depicted in the picture below.

Custom function result-Formula to Find Bold Text in Excel

Read More: How to Find If Cell Contains Specific Text in Excel


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.

vba-Formula to Find Bold Text in Excel

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

macro

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.

Provide a range

Step 4: All the bold entries get highlighted as you can see in the latter screenshot.

Final result

You can assign multiple ranges to find bold texts. This macro can also find bolden values within a given range.


Download Excel Workbook


Conclusion

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.


Related Articles

<< Go Back to Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo