Method 1 – Using the Find and Replace Feature to Remove Bullets
- Select all the cells with bullets.
- Press CTRL + H to open the Find and Replace dialog box.
- In Find what, place your cursor, press ALT and enter 0149.
- A bullet point will be inserted.
- Leave Replace with blank.
- Click Replace All.
All bullets are replaced with blanks:
The dialog box displays “All done. We made 8 replacements.”
- Click OK.
This is the output.
Read More: How to Add Bullets in Excel Cell
Method 2 – Combining the CLEAN and the SUBSTITUTE Functions to Remove Bullets
Combine the CLEAN and the SUBSTITUTE functions.
- Enter the following formula in D5.
=CLEAN(SUBSTITUTE(B5,"•",""))- Press ENTER.
Formula Breakdown
- 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 in B5.
Drag down the Fill Handle to see the result in the rest of the cells.
All the bullets were removed.
Read More: Excel Bullets and Numbering Greyed Out
Method 3 – Using the RIGHT and the LEN Functions to Remove Numbering
Combine the RIGHT and the LEN functions.
- Enter the following formula in D5.
= RIGHT(B5, LEN(B5)-2)- Press ENTER.
Formula Breakdown
- B5 contains numbering.
- The LEN function calculates the length of the text in B5.
- LEN(B5)-2 removes the first 2 characters from the text in B5.
- The RIGHT function returns the rest of the characters from the right side of the text in B5.
- Drag down the Fill Handle to see the result in the rest of the cells.
All numbering was removed.
Practice Section
Practice here.
Download Practice Workbook
Download the Excel file.
Related Articles
- Add Bullet Points in Excel Text Box
- How to Add Multiple Bullet Points in Excel Cell
- How to Align Bullet Points in Excel
<< Go Back to Bullets and Numbering in Excel | Worksheet Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
