How to Change Semicolon to Comma in Excel (6 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Preparing Excel worksheet and have given semicolon mistakenly instead of a comma in your texts? Want to change semicolon to comma but find it difficult to do the task manually in Excel? Even then, want to change the semicolon to comma but submission time is knocking at your door?

Don’t worry! This tutorial will pave the quickest ways to do your task. In this article, I will show you how to change semicolon to comma in Excel.


How to Change Semicolon to Comma in Excel: 6 Ways

Let’s say, we have got a dataset of some texts with semicolons between them. The semicolons have been used mistakenly instead of comma and now we want to change the semicolon to the comma.

Dataset to Change semicolon to Comma in Excel

You can do it manually but it is not a smart approach in case your data contains too many texts and semicolons in your worksheet and at the same time it is very time-consuming. But, Excel has features and functions to do this task quickly.

In this section, you find 6 exclusive ways to change semicolon to comma in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Using Find and Replace Command

Our aim is to remove the semicolon and replace it with comma. One of the most popular ways to do this task is to use the Find and Replace command. This common find any characters or texts within a referred cell and replace them with the acquainted characters or texts. In order to serve this purpose, proceed with the following steps.

📌 Steps:

  • First of all, copy the data range which contains semicolons in them to a new column and select the range of the data.
  • Then, press CTRL+H and the Find and Replace dialog box will appear on the worksheet.
  • Here, type a semicolon (;) in the Find What field and put a comma (,) in the Replace with field> then click Replace All.

Find & Replace Command to change Semicolon to Comma in Excel

  • As soon as you click Replace All, all the semicolons in your selected cells will be replaced with commas.


2. Applying SUBSTITUTE Function to Change Semicolon

The SUBSTITUTE function finds a text or character within a text string and replaces it with a new text or character. So this function can easily change semicolon to comma as it. Follow the steps below to perform the task.

📌 Steps:

  • Firstly, create a new column where you want to put the text changed with a comma.
  • Then, type the following formula in the cell.

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

Here,

  • B5 = The reference cell containing semicolon

  • Here, press ENTER and the cell will replace the semicolon with a comma.
  • Now, drag the Fill Handle tool to Autofill the formula down the cells.

  • Finally, all the cells you have copied the formula will change semicolons to commas.

SUBSTITUTE function to Change Semicolon to Comma in Excel

Read More: How to Change Comma Style in Excel


3. Combining CHAR with SUBSTITUTE Function

According to ASCII, there are specific characters for code numbers from 1 to 255. The CHAR function returns a specific character when a valid number is applied to it. We will use the CHAR function nested in the SUBSTITUTE function to change the semicolon to a comma. The character code of the comma will be applied in the CHAR function.  Let’s start the procedure.

📌 Steps:

  • Firstly, select a cell and type the following in it.

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

Here,

  • B5 = The reference cell containing semicolon
  • 44 = character code of Comma

  • Then, press ENTER and drag the formula down and all the cells will replace semicolon with comma.

CHAR & SUBRTITUTE Functions to change semicolon to comma in Excel

 Formula Breakdown

44 is the character code of Comma.

So, CHAR(44) returns a Comma.

And the SUBSTITUTE function replaces the Semicolon (;) with Comma (,)


4. Using REPLACE and SEARCH Functions

The REPLACE function has similarities with the SUBSTITUTE function considering the task they perform. This function performs the replacement based on the starting number of the character of the part to be replaced and the number of characters that have to be replaced. Nesting the SEARCH function in the REPLACE function will make the formula dynamic. So, let’s start the process like the one below.

📌 Steps:

  • At first, select a cell where you want the replacement, apply the following formula, and drag it down.

=REPLACE(B5,SEARCH(";",B5),1,",")

Here,

  • B5 = The reference cell containing semicolon
  • 1 = number of characters that have to be to be replaced

  • Then, all the cells will show you the desired text with a replacement of semicolons with commas.

REPLACE & SEARCH Functions to Change Semicolon to Comma in Excel

 Formula Breakdown

SEARCH finds the semicolon (;) in cell B5 and returns its position.

So, the Output for SEARCH(“;”,B5) is=> 12

REPLACE(B5,SEARCH(“;”,B5),1,”,”) = REPLACE(B5,12,1,”,”) replaces 1 character from the 12th position of the cell B5.

So, final Output=> Eat burger , no diet today


5. Combination of Multiple Functions to Change Semicolon

We will now apply a combination of multiple functions to change the semicolon in our text with commas. To serve our purpose, we will combine the CONCATENATE, LEFT, RIGHT, and SEARCH functions. Proceed like the steps below.

📌 Steps:

  • First of all, apply the formula in a selected cell to let the cell replace the semicolon with a comma.

=CONCATENATE(LEFT(B5,SEARCH(";",B5)-1)&","&RIGHT(B5,LEN(B5)-SEARCH(";",B5)))

Here,

  • B5 = The reference cell containing semicolon
  • Then, drag the formula to all cells you want your desired output.

 Formula Breakdown

SEARCH finds the semicolon (;) in cell B5 and returns its position.

So, the Output for SEARCH(“;”,B5) is=> 12

LEFT(B5,SEARCH(“;”,B5)-1) = LEFT(B5,12-1) = LEFT(B5,11) returns 11 character from the left of the cell B5.

Output=> Eat burger 

LEN function returns the number of characters in a text string. So, LEN(B5) returns 26

RIGHT(B5,LEN(B5)-SEARCH(“;”,B5)) = RIGHT(B5,26-12) = RIGHT(B5,14) returns 14 character from the right side of the cell B5.

Output=> no diet today

CONCATENATE(LEFT(B5,SEARCH(“;”,B5)-1)&”,”&RIGHT(B5,LEN(B5)-SEARCH(“;”,B5)))
= CONCATENATE(Eat burger&”,”&no diet today)
gives the final output by concatenating “Eat burger” and “no diet today” by putting a comma between them.

So, final Output=> Eat burger , no diet today


6. With Text to Column Wizard and CONCATENATE Function

We will now apply the Text to Column Wizard and CONCATENATE function together to meet our demand. By using the Text to Column Wizard, we will split the portions separated by the semicolon and then add a comma with the CONCATENATE function. Proceed like the steps below.

📌 Steps:

  • At first, select the range of data, go to the Data tab> click the Text to Column feature from the Data Tools group.

Text to Column feature

  • Then, the Convert Text to Column Wizard (Step 1 of 3) box will appear.
  • Here, keep the Delimited field marked (it is marked by default) and click Next.

  • Now, in Step 2, mark the Semicolon field in the Delimiter section and click Finish.

  • Hence, you will see that the portions separated by semicolons are now split into two columns.

  • Now, add a comma using the CONCATENATE function. Type the formula in a cell where you want to add a comma.

=CONCATENATE(B5,",",C5)

Here,

  • B5 = The first split cell
  • C5 = The second split cell

  • After that, press ENTER and drag the formula to let all the cells add a comma between the two portions.

CONCATENATE function to Change Semicolon to Comma in Excel


Download Practice Workbook

You can download the practice book from the link below.


Conclusion

In this article, I have shown you some methods to change semicolon to comma in Excel. Hope the article helped you. If you have better ways, questions, or feedback regarding this article, please don’t forget to share them in the comment box. Have a great day!


Related Articles


<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo