How to Remove Semicolon in Excel?

Method 1 – Removing Ending Semicolon from Cells

In the dataset below (B4:B9) containing some Microsoft Product names, there is a semicolon at the end of every cell value. Let’s use an Excel formula with the combination of LEFT function, LEN function & RIGHT functions to remove them.

STEPS:

In cell C5, enter the following formula:

=LEFT(B5,LEN(B5)-(RIGHT(B5)=";"))

Press Enter and use the Fill Handle tool to autofill the cells below.

Method 2 – Using ‘Find and Replace’ Feature to Remove Semicolons

The ‘Find and Replace’ only works on cells containing text, so is suitable for removing semicolons in Excel.

In the following dataset (B4:B9) of Microsoft Products, let’s replace every semicolon with a comma.

STEPS:

Select the dataset (B4:B9).
Go to the Home tab.
Select the Find & Select drop-down from the Editing section.
Click on Replace.

A Find and Replace message box pops up.

Go to the Replace tab.
In the Find what box, type ‘;’.
In the Replace what box, type ’,’.
Select the Replace All option.

A confirmation window pops up.

Click on OK.

All the semicolons are replaced with commas.

Method 3 – Using the SUBSTITUTE Function to Remove Semicolons

The SUBSTITUTE function replaces specified old text with specified new text. In our dataset (B4:B9) of Microsoft Products, let’s use the SUBSTITUTE function to replace the semicolons with commas and return the results in the range C4:C9.

STEPS:

Select cell C5 and enter the following formula:

=SUBSTITUTE(B5,";",",")

Press Enter and use the Fill Handle tool to copy the formula to the cells below.

All semicolons are replaced successfully with commas.

Method 4 – Using the ‘Text to Columns’ Wizard to Remove Semicolons and Separate Text into Columns

The ‘Text to Column’ wizard separates text cells into multiple columns using specified delimiters. In the below dataset (B4:D9) of Microsoft Products, let’s remove the semicolons by splitting the text into columns (PRODUCT1 & PRODUCT2) using the semicolons as delimiters.