# How to Add Single Quotes and Comma in Excel Formula (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we might sometimes need to add single quotes and commas. Adding single quotes and commas is an easy task. In this article, I will show you four quick and suitable ways to add single quotes and comma in excel formula like CHAR, and CONCATENATE. And also we create a user-defined function in Excel VBA Macro to add single quotes and comma.

## How to Add Single Quotes and Comma in Excel Formula: 4 Easy Ways

Go through the following easy methods to enhance your knowledge about adding single quotes and commas in Excel using formulas. Hereâ€™s an overview of the dataset for our todayâ€™s task.

### 1. Use CHAR Function to Add Single Quotes and Comma

You can add single quotes and commas using the CHAR function. This is an easy and time-saving task also. From our dataset, we will concatenate two cells with single quotes and commas using the CHAR function. Letâ€™s follow the instructions below to learn!

Step 1:

• First of all, select cell D5.

• Hence, write down the below CHAR function in the selected cell. The CHAR function is,
=CHAR(39) & B5 & CHAR(39) & CHAR(44) & CHAR(39) & C5 & CHAR(39)
• Where CHAR(39) returns single quotes and CHAR(44) returns comma between cells B5 and C5.

• Further, simply press ENTER on your keyboard.
• As a result, you will get â€˜Appleâ€™,â€™USAâ€™ as the return of the CHAR function.

Step 2:

• After that, AutoFill the CHAR function to the rest of the cells in column D which has been given in the below screenshot.

### 2. Merge CONCATENATE and CHAR Functions to Add Single Quotes and Comma

Now, we will add single quotes and commas applying both the CONCATENATE and the CHAR functions. This is an easy and time-saving task also. From our dataset, we will concatenate two cells with single quotes and commas applying both the CONCATENATE and the CHAR functions. Letâ€™s follow the instructions below to learn!

Step 1:

• First of all, select cell D5.
• Hence, write down the below CONCATENATE and the CHAR functions in the selected cell. The CONCATENATE and the CHAR functions are,
=CONCATENATE(CHAR(39), B5, CHAR(39), CHAR(44), CHAR(39), C5, CHAR(39))

Formula Breakdown:

• Inside the CONCATENATE function, the CHAR(39) returns single quotes and CHAR(44) returns a
• The CONCATENATE function concatenates cells B5 and C5.

• Further, simply press ENTER on your keyboard.
• As a result, you will get â€˜Appleâ€™,â€™USAâ€™ as the return of the CONCATENATE and the CHAR functions.

Step 2:

• After that, AutoFill the CONCATENATE and the CHAR functions to the rest of the cells in column D which has been given in the below screenshot.

### 3. Apply Ampersand to Add Single Quotes and Comma in Excel Formula

In this method, You will learn how to add single quotes and commas using the Ampersand symbol. This is an easy and time-saving task also. From our dataset, we will add single quotes and commas using the Ampersand symbol. Letâ€™s follow the instructions below to learn!

Step 1:

• First of all, select cell D5.
• After that, write down the below formula with the Ampersand symbol in the selected cell. The formula is,
="'"&B5&"'"& "," &"'"&C5&"'"

• Hence, simply press ENTER on your keyboard.
• As a result, you will get the output of the formula, and the output is â€˜Appleâ€™,â€™USAâ€™.

Step 2:

• Further, AutoFill the formula to the rest of the cells in column D which has been given in the below screenshot.

### 4. Create User Defined Function Using Excel VBA Code to Add Single Quotes and Comma

Last but not the least, Iâ€™ll show how to add single quotes and commas in Excel by using a simple VBA code. Itâ€™s very helpful for some particular moments and a time-saving way also. From our dataset, we will add single quotes and commas. Letâ€™s follow the instructions below to add single quotes and commas!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer â†’ Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications â€“ Add Single Quotes and Comma will instantly appear in front of you.
• From that window, we will insert a module for applying our VBA code.
• To do that, go to,

Insert â†’ Module

Step 2:

• Hence, theÂ Add Single Quotes and Comma module pops up. In theÂ Add Single Quotes and Comma module, write down the below VBA.
Function ColumntoList(ColRange As Range)
Dim ListOutput
Dim cell As Variant
For Each cell In ColRange
If Not IsEmpty(cell.Value) Then
ListOutput = ListOutput & "'" & cell.Value & "',"
End If
Next
ColumntoList = Left(ListOutput, Len(ListOutput) - 1)
End Function

• Hence, run the VBA To do that, go to,

Run â†’ Run Sub/UserForm

Step 3:

• We will now go back to the worksheet and write the following code in cell C5.
=ColumntoList(B5:B10)

• Upon pressing ENTER, we will get the comma-separated list with single quotes and commas around each cell value of the Product column in cell C5.

## Things to Remember

ðŸ‘‰ #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

ðŸ‘‰ #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

## Conclusion

I hope all of the suitable methods mentioned above to add single quotes and commas will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

## Related Articles

<< Go Back to Quotes in Excel |Â Concatenate ExcelÂ |Â Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF