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.

add single quotes and comma in excel formula


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.

Use CHAR Function to Add Single Quotes and Comma

  • 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.

Use CHAR Function to Add Single Quotes and Comma

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.

Read More: How to Concatenate Single Quotes in Excel


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.

Merge CONCATENATE and CHAR Functions to Add Single Quotes and Comma

  • 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.

Merge CONCATENATE and CHAR Functions to Add Single Quotes and Comma

Read More: How to Add Single Quotes in Excel for Numbers


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&"'"

Apply Ampersand to Add Single Quotes and Comma in Excel Formula

  • 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.

Apply Ampersand to Add Single Quotes and Comma in Excel Formula


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

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

  • 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

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

  • 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)

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

  • 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.

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


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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo