Bullets are often used in Excel for presentation purpose. However, it can be a bit annoying especially when it comes to removing it. Removing them one by one can easily cost you an hour if you have a large data set.
In this article, I will show you how to remove bullet points or numberings in Excel.
If you have the following questions, I suggest you reading this until the end.
- How to remove bullet points or numberings in Excel?
- How to get rid of bullets in Excel?
- How would you remove the bullets from the list in Excel?
You might also be interested in How to Stack Multiple Columns into One Column.
Remove bullets/numberings in Excel by “Find & Replace”
Here is a name list with bullet points ahead of each name. I will show you how to remove all the bullets by a Excel feature called “Find & Replace”.
What we would like to do
To remove bullets out of the selected cell ranges
You might also be interested in How to prevent duplicate entries in Excel?
Step 1: Highlight the cell ranges you wish to remove bullets from
You can skip this step if you wish to remove bullets from the whole Excel worksheet. Highlighting the cell ranges is simply making sure we didn’t remove bullet from non-targeting cells.
Step 2: “Home” > “Find & Select” > “Replace”
Now you should be able to see the “Find and Replace” dialog.
The shortcut to open the “Find and Replace” dialog is Ctrl H.
Step 3: Insert a bullet into the “Find what” field and leave the “Replace with” field empty
To insert the bullet into the “Find what” field, there are two ways.
- Hold the Alt key and press 0 1 4 9 on the numeric keypad. You should see a bullet inserted when you release your finger. (This only works for people with a numeric keypad)
- Copy a bullet from your cell and paste it into the “Find what” field. (Perfect for people failed the first way)
Step 4: Press “OK” and “Close”
Result
Now the bullets within the selected cell ranges are all gone.
You might also be interested in 6 Ways To Converting Text To Number Quickly In Excel
Remove bullets in Excel by Formula
Here is a name list with bullet points ahead of each name.
What we would like to do
To create a new list without bullets
You might also be interested in How to prevent duplicate entries in Excel?
Formula in cell B2
=CLEAN(SUBSTITUTE(A2,"•",))
If your cells contain symbols others than bullet, just replace the bullet in the formula.
To apply the formula to the remaining cells, simply copy the cell and paste it down.
How does the formula work
CLEAN function removes all non-printable characters from text. This is a brilliant function for data cleaning. I have included CLEAN function here to help you remove some unwanted characters.
SUBSTITUTE function replaces specific text in a text string. This is like a “function” version of the “Find & Replace” Feature of Excel.
You may wonder how to choose between method 1 and method 2. I will suggest you to use method 2 if you want to keep the source data. For method 1, the source data would be gone since you directly make changes on the cell ( of course you can copy the source data elsewhere in the worksheet to keep it original).
If there is a likelihood that you will change the source data, I highly suggest you going for method 2 because of its flexibility. If you chose method 1, you will need to do the “Find & Replace” thing every time you make a change. Instead, the formula will update itself whenever you make changes to source data if you opted for method 2.
You might also be interested in 6 Ways To Text To Number Quickly In Excel
Remove numberings in Excel by Formula (single digit)
Here is a name list with numberings ahead of each name.
What we would like to do
To create a new list without numberings
You might also be interested in How to prevent duplicate entries in Excel?
Formula in cell B2
=RIGHT(A2,LEN(A2)-3)
To apply the formula to the remaining cells, simply copy the cell and paste it down.
How does the formula work
LEN function returns the length of a string.
LEN(A2) counts number characters inside cell B2 and return 8.
LEN(A2) – 3 = 8 – 3 = 5
RIGHT function returns the rightmost characters in a text string, based on the number of characters you specify.
RIGHT(A2, 5) extract 5 text strings from the cell A2 starting from the right side of cell A2.
So, the formula returns “Tommy”
Attention! If the numbering is not followed by a space, the formula would have been
=RIGHT(A2,LEN(A2)-2)
Things to be aware of
You will need to edit the formula if the number is not single digit
This is how the above formula result in when it comes to non-single digit.
What should you do if you want to remove numbering that is more than one digit?
You just need to twist the formula a bit.
Formula for Two-digit
=RIGHT(A2,LEN(A2)-4)
Formula for Three-digit
=RIGHT(A2,LEN(A2)-5)
Remove numberings in Excel by Formula (multiple digit)
According to the previous section, you will have to apply different formula when you have different digit. It could lead to inconsistency in formula thus more likely to make mistakes.
To tackle this problem, I have made a more comprehensive formula.
Formula in cell B2
=RIGHT(A2,LEN(A2)-(FIND(".",A2)+1))How does the formula work
This formula is indeed very similar to the above formula. The only difference is that we used FIND function.
The FIND function help us locate a substring in a string. Here we used FIND function to locate the first dot in the cell and return the number.
The first dot in cell B2 is the second character of the cell so it returns 2.
Since the numbering is followed by a space, I added one to the result.
Excel will then extract strings from the right side and return “Natalie”.
I recommend you to refer to Remove numberings in Excel by Formula (single digit) for detailed explanation of the formula.
You might also be interested in 6 Ways To Text To Number Quickly In Excel
You might also be interested in How to Mass Delete hidden rows/ columns in Excel
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
