How to Add Double Quotes and Comma in Excel with CONCATENATE

Get FREE Advanced Excel Exercises with Solutions!

Adding double quotes, commas, or any other special characters to some cells in Excel is a frequent need for Excel users. It is tiring and time-consuming to accomplish this manually at every required cell. Rather, it will be quicker and handier, if we can do this by using formulas or any shortcut tricks. In this article, I will demonstrate to you 2 simple and easy formulas to add double quotes and comma in Excel concatenate.


Download Practice Workbook

You can download and practice from our workbook here for free!


2 Easy Formulas to Add Double Quotes and Comma in Excel with CONCATENATE Function

Say, you have a dataset of 2 letters from different languages. Now, you want to write those letters in double quotes (“ ”), separated by a comma (,) and add their language name respectively. To make it clearer, say, you have two letters A and B given. Now, you want to get your output as “A”, ”B” are English letters.

Inputs to Add Double Quotes and Commas in Excel Concatenate

To accomplish this, you can follow any of the 4 simple methods given below. We have used the Office 365 version of Microsoft Excel when showing these methods. But you can use all of these methods in any other version of Excel too. If you can’t, please let us know in the comment section.


1. Insert Double Quotes and Comma within CONCATENATE Formula

Another effective way to add double quotes and commas in Excel concatenate is to use the CONCATENATE function. Go through the steps below to learn this.

📌 Steps:

  • At the very beginning, select the E5 cell.
  • Afterward, write the following formula in the formula bar. Subsequently, hit the Enter button.
=CONCATENATE("""",B5,"""",", ","""",C5,""""," are ", D5," letters ")

Insert the CONCATENATE Function to Add Double Quotes and Comma in Excel Concatenate

  • Consequently, you will get the desired result for the 5th row’s inputs.
  • At this time, place your cursor in the bottom-right position of the cell and drag the fill handle below upon its appearance.

Drag Fill Handle to Copy the Same Formula

Thus, the formula will be copied to all cells below and you will get your desired result for the whole input dataset. And, the result should look like this.

Inserted Double Quotes and Comma in Excel Concatenate

Read More: How to Add Double Quotes in Excel Concatenate (5 Easy Ways)


2. Combine CHAR and CONCATENATE Functions

Besides, you can also combine the CHAR function with the CONCATENATE function to achieve your desired result.

📌 Steps:

  • Initially, click on the E5 cell.
  • Afterward, insert the following formula and hit the Enter button.
=CONCATENATE(CHAR(34),B5,CHAR(34),",",CHAR(34),C5,CHAR(34)," are ",D5," letters ")

Combine the CONCATENATE and CHAR Function to Add Double Quotes and Comma in Excel Concatenate

🔎 Formula Breakdown:

  • CHAR(34),B5,CHAR(34),”,”,CHAR(34),C5,CHAR(34),” are “,D5,” letters “

It returns double quotes and the following cell values. But the result would not be shown this time as the format is unrecognizable to show any result.

Result: Nothing.

  • =CONCATENATE(CHAR(34),B5,CHAR(34),”,”,CHAR(34),C5,CHAR(34),” are “,D5,” letters “)

It concatenates every value from the previous breakdown results.

Result: “A”, “B” are English letters.

  • Consequently, you will get your desired result for the first inputs.
  • Now, for all the desired outputs you need to copy the same formula.
  • To do this, place your cursor in the bottom right position of the E5 cell.
  • Subsequently, a black fill handle will appear. Drag it downward to copy the same formula below.

Drag the Fill Handle to Copy the Same Formula

Thus, all your output cells will now contain your desired outputs as per their inputs. And, the result would look like this.

Added Double Quotes and Comma in Excel Concatenate

Read More: How to Concatenate Single Quotes in Excel (5 Easy Ways)


More Ways to Add Double Quotes and Comma in Excel

1. Use Ampersand (&) Operator Functionality

You can use the Ampersand (&) operator functionality to add double quotes and commas in Excel concatenate. Follow the steps below to do this.

📌 Steps:

  • First and foremost, click on the C5 cell.
  • Subsequently, insert the following formula and press the Enter button.
=""""&B5&""""&", "&""""&C5&""""&" are "&D5&" letters "

Use the Ampersand (&) Operator to Add Double Quotes and Comma in Excel Concatenate

  • As a result, you will get your desired result in the E5 cell.
  • Now, place your cursor in the bottom right position of the cell.
  • Subsequently, a black fill handle will appear. Drag it downward to copy the same formula.

Drag the Fill Handle Downward to Copy the Same Formula

Consequently, you will be able to add double quotes and commas in Excel concatenate. For instance, the outcome should look like this.

Added Double Quotes and Comma in Excel ConcatenateRead More: How to Add Single Quotes and Comma in Excel Formula (4 Ways)


2. Merge CHAR Function and Ampersand (&) Operator

Moreover, you can merge the CHAR function with the Ampersand (&) operator too.

Follow the steps below to accomplish this.

📌 Steps:

  • First, left-click your mouse on the E5 cell.
  • Next, enter the following formula in the formula bar.
=CHAR(34)&B5&CHAR(34)&","&CHAR(34)&C5&CHAR(34)&" are "&D5&" letters "
  • Subsequently, press the Enter button.

Merge the Ampersand (&) Operator and the CHAR Function

  • As a result, you will get your desired result for the first inputs.
  • Afterward, place your cursor in the bottom right position of the cell.
  • Subsequently, a black fill handle will appear. Now, drag it down to copy the same formula.

Drag Fill Handle Below to Copy Same Formula

As a result, you will get all your desired results for all the inputs. And, for example, the outcome should look like this.

Added Double Quotes and Comma in Excel Concatenate

Read More: How to Convert Column to Comma Separated List With Single Quotes


Conclusion

In a nutshell, in this article, I have shown you 2 effective and simple formulas to add double quotes and commas in Excel concatenate. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. Besides, you are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo