Rafiul Hasan
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.
Download Practice Workbook
You can download the practice book from the link below.
6 Ways to Change Semicolon to Comma in Excel
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.
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.
- As soon as you click Replace All, all the semicolons in your selected cells will be replaced with commas.
Read More: How to Insert Comma Between Words in Excel (4 Simple Methods)
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 for performing 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.
Read More: How to Add Comma in Excel Between Names (4 Suitable Ways)
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.
💡 Formula Breakdown
44 is the character code of Comma.
So, CHAR(44) returns a Comma.
And the SUBSTITUTE function replaces the Semicolon (;) with Comma (,)
Read More: Use Comma in Excel Formula (2 Suitable Ways)
Similar Readings
- How to Add Thousand Separator in Excel Formula
- Put Comma After 2 Digits in Excel (9 Quick Methods)
- How to Put Comma after 3 Digits in Excel (7 Useful Methods)
- Put Comma in Numbers in Excel (7 Easy Ways)
- How to Separate Address in Excel with Comma (3 Easy Methods)
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 and apply the following formula to it 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.
💡 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.
- 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.
Read More: How to Insert Comma in Excel for Multiple Rows (3 Suitable Ways)
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. For more queries, kindly visit our website ExcelDemy. Have a great day!
