Nuraida Kashmin
Sometimes we need to remove semicolon from the worksheet in Excel. Excel has some quick features as well as functions to do so. In this article, we are going to see them with some beautiful examples and explanations.
Practice Workbook
Download the following workbook and exercise.
4 Ways to Remove Semicolon in Excel
1. Remove Ending Semicolon from Cell in Excel
Assuming, we have a dataset (B4:B9) of some Microsoft Product names. There is a semicolon at every end of the cell value. We are going to use an Excel formula with the combination of LEFT function, LEN function & RIGHT function to remove this ending semicolon.
STEPS:
- First, select Cell C5.
- Now type the formula:
=LEFT(B5,LEN(B5)-(RIGHT(B5)=";"))
- Then hit Enter and use the Fill Handle tool to autofill the next cells.
Read More: How to Remove Special Characters in Excel (4 Methods)
2. Use of ‘Find and Replace’ Feature Removing Semicolon
Excel has a lot of features that can save time. ‘Find and Replace’ is one of them. But it only works on text containing cells. We can use this for removing semicolons in Excel. Suppose, we have a dataset (B4:B9) of Microsoft Products. We are going to remove every semicolon and replace it with a comma.
STEPS:
- In the beginning, select the dataset (B4:B9).
- Next, go to the Home tab.
- Now select the Find & Select drop-down from the Editing section.
- Click on the Replace.
- A Find and Replace message box pops up. Go to the Replace tab.
- In Find what blank box, type ‘;’.
- Again, in the Replace what box, type ’,’.
- Then select Replace All option.
- A confirmation window pops up. Click on OK.
- In the end, we can see that all the semicolons are replaced with commas.
Related Content: How to Remove Numeric Characters from Cells in Excel (5 Methods)
Similar Readings:
- Excel Remove Characters From Right (5 ways)
- How to Remove Last Character in Excel(Easiest 6 Ways)
- Remove Dashes from Phone Number in Excel(4 Ways)
- How to Remove Dashes in Excel (3 Methods)
- How to Remove Blank Characters in Excel (5 Methods)
3. Insert SUBSTITUTE Function to Remove Semicolon
Excel SUBSTITUTE function replaces the specific old text with the new one. Here we have a dataset (B4:B9) of Microsoft Products. We are going to insert the SUBSTITUTE function to replace the semicolon with the comma and return the result in the range C4:C9.
STEPS:
- Select Cell C5 at first.
- Then write down the formula:
=SUBSTITUTE(B5,";",",")
- After that, press Enter and use the Fill Handle tool to see the result.
Read More: How to Remove Specific Characters in Excel ( 5 Ways)
4. Excel ‘Text to Columns’ Wizard to Remove Semicolon and Separate Text into Column
Sometimes we need to remove semicolons and also separate the text cells into multiple columns. Excel has a beautiful feature for that, called the ‘Text to Column’ wizard. In the below dataset (B4:D9) of Microsoft Products, we are going to remove the semicolons. Then split them into columns (PRODUCT1 & PRODUCT2) based on the semicolons by applying the ‘Text to Columns’ wizard. Let’s see how it works.
STEPS:
- First, select the data range B5:B9.
- Now go to the Data tab.
- From the Data Tools section, select the Text to Columns option.
- We can see the Convert Text to Columns Wizard Step 1 window.
- Select the Delimited option.
- Next click on Next.
- The Convert Text to Columns Wizard Step 2 window pops up.
- Put a tick mark on the Other option from the Delimiters section and type ‘;’ on the blank box.
- We can see the preview in the Data Preview section.
- Then click on Next.
- Finally, we can see the Convert Text to Columns Wizard Step 3 window. Select General from the Column data format section.
- After that, select the Destination where we want to see the result.
- In the Data Preview section, we can see the preview of our final dataset.
- In the end, click on the Finish.
- Here we can see the semicolons are removed and texts are separated into columns.
Read More: Remove Characters after a Specific Character in Excel (4 Tricks)
Conclusion
These are the easy ways to Remove the Semicolon in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
