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

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.


Download Practice Workbook

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


3 Ways to Remove Bullets and Numbering in Excel

1. Using Find and Replace to Remove Bullets

You can remove the bullets out of 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.


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.


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.


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. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo