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.
How to Add Double Quotes and Comma in Excel with CONCATENATE Function: 2 Easy Formulas
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
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
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
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 Add Double Quotes in Excel
Download Practice Workbook
You can download and practice from our workbook here for free!
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. Thank you!
How can you quickly add single quotes and separate with a comma, for a single data set and column. I have data from SQL where I only need to utilize data from 1 column when I export to Excel, but I need to add single quotes and separate with a comma. Any help would be great.
Hello TOM,
Thanks for your comment.
Suppose, you have the following dataset with data in a single column in cells B5:B8. You want to add single quotes and separate them with a comma in cell B11. So, use the following formula with the CONCATENATE function in cell B11:
=CONCATENATE(“‘”,B5,”‘”,”, “,”‘”,B6,”‘”,”, “,”‘”,B7,”‘”,”, “,”‘”,B8,”‘”)
Press Enter to get the desired output.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy