Mrinmoy Roy
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.
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.
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.
❸ Drag the Fill Handle from cell D5 to D12.
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.
❸ 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.
