Naimul Hasan Arif
We like decorating our things, even in case of a dataset. Adding comma between city and state will help us to differentiate those two quite easily. It will also enhance the beauty. In this article, I am going to explain 6 smart ways to add a comma between city and state in Excel. I hope it will be helpful for Excel users.
For more clarification, I am going to use a dataset containing Name, City, and State columns.
Download Practice Workbook
6 Amazing Ways to Add a Comma between City and State in Excel
1. Using TEXTJOIN Function to Add a Comma
One of the simplest ways to add comma between city and state is to use the TEXTJOIN function.
Steps:
- Select a preferred cell to have the output. In my case, I chose cell E5 to have the desired result.
- Input the following formula in that cell.
=TEXTJOIN(", ",TRUE,C5:D5)Here, the TEXTJOIN function adds all the cells in the range C5:D5 with a comma after each cell value.
- Press ENTER to have the output.
- Use Fill Handle to AutoFill the rest cells.
Read More: How to Use Comma in Excel Formula (2 Suitable Ways)
2. Ampersand Operator to Add a Comma Between City and State
Another simple and easy way to add comma between city and state is to use the Ampersand Operator (&) in a formula.
Steps:
- Firstly, pick a cell to have the output. Here, I chose cell E5 to have the desired result.
- Next, input the following formula in that cell.
=C5&", "&D5Here, the Ampersand Operator adds all the cells C5 and D5 with a comma.
- Hit ENTER to have the result.
- AutoFill the rests.
Read More: How to Insert Comma Between Words in Excel (4 Simple Methods)
3. Adding Comma with Find and Replace Feature
Find and Replace is another amazing feature under Find & Select to add comma between city and state. But the condition is the name of the city or state must be only in one word.
Steps:
- Copy the entire Location column and Paste it into the Output
- Now, go to the Home tab.
- Then, click Find & Select from the ribbon.
- Followingly, pick the Replace option.
A Find and Replace wizard will appear.
- Input Space in Find what section.
- After that, input Comma in Replace with section.
- Then, click on Replace All.
A confirmation box will appear.
- Click OK to finish the process.
Thus, we can have our desired output.
Read More: How to Add Comma in Excel Between Names (4 Suitable Ways)
4. SUBSTITUTE Function to Add a Comma Between City and State
We can also use the SUBSTITUTE function to add comma between city and state. But the condition here is the name of the city or state must be only in one word.
Steps:
- Select a preferred cell to have the output. In my case, I chose cell E5 to have the desired result
- Next, apply the following formula in that cell.
=SUBSTITUTE(C5," ",", ")Here, the SUBSTITUTE function replaces the space in cell C5 with a comma.
- Press ENTER to have the output.
- Finally, use Fill Handle to AutoFill the rest cells.
Read More: How to Insert Comma in Excel for Multiple Rows (3 Suitable Ways)
5. Applying CONCATENATE Function to Add a Comma
As the CONCATENATE function combines the multiple cells in one cell, this function can also be used to add comma between city and state.
Steps:
- Firstly, choose a preferred cell to have the output. Here, I chose cell E5 to have the desired result.
- Now, input the following formula in that cell.
=CONCATENATE(C5,", ",D5)Here, the CONCATENATE function adds the cells C5 and D5 with a comma.
- Afterward, hit ENTER to have the result.
- Now, AutoFill the remaining cells.
Read More: How to Add Comma in Excel to Concatenate Cells (5 Easy Ways)
6. Utilizing Flash Fill Feature to Add a Comma
Flash Fill, no doubt, is the simplest way to add comma between city and state. Let’s see the procedure.
Steps:
- Write manually in the first two cells in the Output column adding city and state with a comma.
- Next, go to the Home tab.
- Choose Data Tools from the ribbon.
- Then, click on Flash Fill to AutoFill the remaining cells.
This is how we can also have our desired result.
Practice Section
For more expertise, you can practice here.
Conclusion
That’s all for the article. In this article, I have tried to elaborate on 6 smart ways to add a comma between city and state in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our ExcelDemy site for more details on Excel.
