How to Remove Semicolon in Excel (4 Methods)

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.

Remove Ending Semicolon from Cell in Excel

STEPS:

  • First, select Cell C5.
  • Now type the formula:
=LEFT(B5,LEN(B5)-(RIGHT(B5)=";"))

Remove Ending Semicolon from Cell in Excel

  • 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.

Use of ‘Find and Replace’ Feature Removing Semicolon

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.

Use of ‘Find and Replace’ Feature Removing Semicolon

  • 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.

Use of ‘Find and Replace’ Feature Removing Semicolon

  • 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:


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.

Insert SUBSTITUTE Function to Remove Semicolon

STEPS:

  • Select Cell C5 at first.
  • Then write down the formula:
=SUBSTITUTE(B5,";",",")

Insert SUBSTITUTE Function to Remove Semicolon

  • 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.

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

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.

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

  • We can see the Convert Text to Columns Wizard Step 1 window.
  • Select the Delimited option.
  • Next click on Next.

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

  • 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.

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

  • 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.


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo