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.
How to Remove Semicolon in Excel: 4 Methods
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 functions 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 Non-Alphanumeric Characters in Excel
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 the Find what blank box, type ‘;’.
- Again, in the Replace what box, type ’,’.
- Then select the 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.
3. Insert SUBSTITUTE Function to Remove Semicolon
Excel the 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.
4. Excel ‘Text to Columns’ Wizard to Remove Semicolon and Separate Text into Column
Sometimes we need to remove semicolons and 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.
Download Practice Workbook
Download the following workbook and exercise.
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.
Related Articles
- How to Remove Parentheses in Excel
- How to Remove Dashes from Phone Number in Excel
- How to Remove Dashes from SSN in Excel
- How to Remove Dashes in Excel
- How to Remove Apostrophe in Excel
- How to Remove Asterisk in Excel
- How to Remove Blank Characters in Excel
- How to Remove Non-Printable Characters in ExcelÂ
<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Have to say, for a change from these usual spammy sites, that was really useful thanks!
You are welcome, G. 🙂