How to Remove Semicolon in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

How to Remove Semicolon in Excel: 4 Methods

1. Remove Ending Semicolon from Cell in Excel

2. Use of ‘Find and Replace’ Feature Removing Semicolon

3. Insert SUBSTITUTE Function to Remove Semicolon

4. Excel ‘Text to Columns’ Wizard to Remove Semicolon and Separate Text into Column

Conclusion

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 Dashes in Excel

Similar Readings

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.

Similar Readings

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.

Similar Readings

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.

Read More: Excel Remove Characters From Right

Download Practice Workbook

Download the following workbook and exercise.

Remove Semicolon.xlsx

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.

How to Remove Semicolon in Excel?