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


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.

How to Add a Comma between City and State in Excel

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&", "&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 (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.

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

How to Add a Comma between City and State in Excel

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

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

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.

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


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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo