How to Add a Comma Between City and State in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Add a Comma between City and State in Excel


How to Add a Comma between City and State in Excel: 6 Ways

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.

How to Add a Comma between City and State in Excel

  • Use Fill Handle to AutoFill the rest cells.

Read More: How to Use Comma in Excel Formula


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&", "&D5

Here, the Ampersand Operator adds all the cells  C5 and D5 with a comma.

How to Add a Comma between City and State in Excel

  • Hit ENTER to have the result.

  • AutoFill the rests.

How to Add a Comma between City and State in Excel

Read More: How to Insert Comma Between Words in Excel


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.

How to Add a Comma between City and State in Excel

A confirmation box will appear.

  • Click OK to finish the process.

Thus, we can have our desired output.

How to Add a Comma between City and State in Excel

Read More: How to Add Comma in Excel Between Names


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.

How to Add a Comma between City and State in Excel

  • Finally, use Fill Handle to AutoFill the rest cells.


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.

How to Add a Comma between City and State in Excel

  • Afterward, hit ENTER to have the result.

  • Now, AutoFill the remaining cells.

How to Add a Comma between City and State in Excel


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.

How to Add a Comma between City and State in Excel

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

How to Add a Comma between City and State in Excel


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

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

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo