How to Remove Bullets and Numbering in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

The usage of bullet points and numbering are pretty rare in MS Excel. You may find the bullets and numbering in your Excel sheets when you brought data from MS Word documents, CSV files, text files, etc. If you don’t want the bullets and numbering in your sheets, you don’t need to worry about this. Because there are multiple ways to remove those bullets and numbering in Excel. Today, I will discuss 3 effective methods to remove bullets and numbering in Excel with ease. So without having any further discussion, let’s dive straight into it.


How to Remove Bullets and Numbering in Excel: 3 Suitable Ways

1. Using Find and Replace to Remove Bullets

You can remove the bullets from your Excel sheets using the Find and Replace dialog box. It’s pretty easy. Now, I’m showing you how to delete the bullets using the Find and Replace dialog box in Excel.

❶ At first, you need to highlight all the cells having the bullets.

❷ Next press CTRL + H to open the Find and Replace dialog box.

❸ In the Find what box, put your cursor. Then press the ALT button and type 0149.

This will insert a bullet point there.

❹ Leave the Replace with box blank.

❺ After that, click on the Replace All button.

Using FInd and Replace to Remove Bullets and Numbering in Excel

So, Excel will replace all the bullets with blanks.

Now, you will see the following dialog box on the screen that displays “All done. We made 8 replacements.

❻ Hit OK to proceed.

Using FInd and Replace to Remove Bullets and Numbering in Excel

Finally, you will see that all the bullets have been removed successfully.

Read More: How to Add Bullets in Excel Cell


2. Combining CLEAN and SUBSTITUTE Functions to Remove Bullets

If you don’t want to use the previous method, you can combine the CLEAN and SUBSTITUTE functions to remove the bullets.

❶ First, insert the following formula in cell D5.

=CLEAN(SUBSTITUTE(B5,"•",""))

❷ Then press ENTER.

Formula Breakdown

  • Cell B5 has a text with a bullet point.
  • The SUBSTITUTE function replaces the bullet point using a null string.
  • The CLEAN function clears all non-printable characters from cell B5.

Combining CLEAN and SUBSTITUTE Functions to Remove Bullets and Numbering in Excel

❸ Drag the Fill Handle from cell D5 to D12.

Combining CLEAN and SUBSTITUTE Functions to Remove Bullets and Numbering in Excel

After that, you will see that all the bullets have been removed by the formula.

Read More: Excel Bullets and Numbering Greyed Out


3. Joining RIGHT and LEN Functions to Remove Numbering

To remove numbering out of your Excel sheets, you can use a formula joining the RIGHT and LEN functions.

Now follow the steps below to remove the numbering.

❶ First, insert the following formula in cell D5.

= RIGHT(B5, LEN(B5)-2)

❷ After that, press ENTER.

Formula Breakdown

  • Cell B5 contains a text having the numbering.
  • The LEN function calculates the length of the text in cell B5.
  • LEN(B5)-2 removes the first 2 characters from the text in cell B5.
  • Finally, the RIGHT function returns the rest of the characters from the right side of the text in cell B5.

Joining RIGHT and LEN Functions to Remove Numbering and Bullets in Excel

❸ Drag the Fill Handle from cell D5 to D12.

Finally, you will see that all the numbering has been removed from the texts.

Note: Every method I have used throughout the article can be used for removing both bullet points and numbering not necessary that you can not use the method apart from the mentioned one.


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, I have discussed how to remove bullets and numbering in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries as soon as possible.


Related Articles


<< Go Back to Bullets and Numbering in Excel | Worksheet 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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo