Tanjim Reza
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.
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 ")
- 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.
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.
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 ")
🔎 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.
Thus, all your output cells will now contain your desired outputs as per their inputs. And, the result would look like this.
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 "
- 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.
Consequently, you will be able to add double quotes and commas in Excel concatenate. For instance, the outcome should look like this.
Read 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.
- 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.
As a result, you will get all your desired results for all the inputs. And, for example, the outcome should look like this.
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!
