Method 1 – Insert Double Quotes and Comma within CONCATENATE Formula
Steps:
- Select the E5 cell.
- Write the following formula in the formula bar. Hit the Enter button.
=CONCATENATE("""",B5,"""",", ","""",C5,""""," are ", D5," letters ")
- Get the desired result for the 5th row’s inputs.
- Place your cursor in the bottom-right position of the cell and drag the fill handle below upon its appearance.
The formula will be copied to all cells below, and you will get your desired result for the whole input dataset. The result should look like this.
2. Combine CHAR and CONCATENATE Functions
Steps:
- Click on the E5 cell.
- 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. 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.
- Get your desired result for the first inputs.
- For all the desired outputs, you need to copy the same formula.
- Place your cursor in the bottom right position of the E5 cell.
- A black fill handle will appear. Drag it downward to copy the same formula below.
All your output cells will now contain your desired outputs per their inputs. The result would look like this.
More Ways to Add Double Quotes and Comma in Excel
Method 1 – Use Ampersand (&) Operator Functionality
Steps:
- Click on the C5 cell.
- Insert the following formula and press the Enter button.
=""""&B5&""""&", "&""""&C5&""""&" are "&D5&" letters "
- Get your desired result in the E5 cell.
- Place your cursor in the bottom right position of the cell.
- A black fill handle will appear. Drag it downward to copy the same formula.
Add double quotes and commas in Excel concatenate. For instance, the outcome should look like this.
Method 2 – Merge CHAR Function and Ampersand (&) Operator
Steps:
- Left-click your mouse on the E5 cell.
- Enter the following formula in the formula bar.
=CHAR(34)&B5&CHAR(34)&","&CHAR(34)&C5&CHAR(34)&" are "&D5&" letters "- Press the Enter button.
- Get your desired result for the first inputs.
- Place your cursor in the bottom right position of the cell.
- A black fill handle will appear. Drag it down to copy the same formula.
Get all your desired results for all the inputs. The outcome should look like this.
Download Practice Workbook
You can download and practice from our workbook here for free!
Related Articles
<< Go Back to Quotes in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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