Excel CONCATENATE: How to add quotes?

• 🖥️ Excel's CONCATENATE function merges multiple text values but has been replaced with CONCAT in newer versions.
• ✍️ To include double quotes in concatenated text, use CHAR(34) or double double-quotes ("").
• 🚀 TEXTJOIN offers more flexibility by automatically handling delimiters and skipping empty values.
• 🔍 Using & instead of CONCATENATE simplifies formulas and improves readability.
• 📊 These techniques streamline data preparation for CSV files, SQL queries, and structured reports.

Mastering Excel CONCATENATE: How to Add Quotes?

The Excel CONCATENATE function is a powerful tool for combining text, but adding quotation marks within the result can be tricky. Excel treats quotes as special characters, leading to formatting issues if used incorrectly. This guide will walk you through different methods to correctly include quotation marks in your formulas, whether using CONCATENATE, CHAR(34), or newer functions like CONCAT and TEXTJOIN.

Understanding the CONCATENATE Function

The CONCATENATE function allows you to merge multiple text values into a single string, making it useful for creating structured outputs like CSV files, formatted reports, and SQL queries.

Syntax of CONCATENATE

The basic syntax is:

=CONCATENATE(text1, text2, ...)

For example, if A1 contains "Hello" and B1 contains "World", the formula:

=CONCATENATE(A1, " ", B1)

Returns:
Hello World

Evolution of CONCATENATE: Why Use CONCAT Instead?

Since Excel 2016, Microsoft introduced CONCAT, which replaces CONCATENATE but remains available for backward compatibility (Microsoft, n.d.).

Unlike CONCATENATE, CONCAT allows array-based input, making it more efficient when working with dynamic data ranges.

Example of CONCAT:

=CONCAT(A1:A3)

This automatically joins the values in cells A1 to A3, eliminating the need to specify each cell individually.

The Problem with Adding Quotes in CONCATENATE

Why Does Excel Misinterpret Quotation Marks?

When attempting to concatenate quoted text, Excel encounters errors due to how it interprets double quotes.

For example, this formula results in an error:

=CONCATENATE("He said, "Hello"")

Why?

• Excel reads the second double quote as the closing character for He said, rather than part of the text.
• This causes incorrect parsing, leading to formula errors.

To fix this issue, you must escape the quotation marks using either double double-quotes or the CHAR(34) function.

Solution: Using Double Quotes in CONCATENATE

Escaping Quotes with Double Double-Quotes

A straightforward way to correctly use quotes in CONCATENATE is to double them ("").

=CONCATENATE("He said, ","""Hello"""")

This outputs:
He said, "Hello"

Each pair of double quotes ("") represents a single double quote in Excel, ensuring the formula interprets them correctly.

Solution: Using CHAR(34) to Insert Quotes

Another method to insert quotes is using CHAR(34), the numeric ASCII representation of a quotation mark (Microsoft, n.d.).

=CONCATENATE("He said, ", CHAR(34), "Hello", CHAR(34))

This also returns:
He said, "Hello"

Why Use CHAR(34)?

• Improves readability – Eliminates confusion from multiple double-quotes.
• Reduces errors – Ensures quotes are always interpreted correctly by Excel.

Using CONCAT Instead of CONCATENATE

For newer Excel versions, it's best to use CONCAT, which follows the same principles but is more robust:

=CONCAT("He said, ", CHAR(34), "Hello", CHAR(34))

Since CONCAT supports array ranges, it's superior to CONCATENATE when working with multiple cells dynamically.

Using TEXTJOIN for More Flexibility

The TEXTJOIN function is even more efficient, allowing customizable delimiters and automatic handling of empty values.

=TEXTJOIN("", TRUE, "He said, ", CHAR(34), "Hello", CHAR(34))

When to Use TEXTJOIN vs. CONCAT

Alternative Methods & Workarounds

Using & Instead of CONCATENATE

Instead of CONCATENATE or CONCAT, concatenation can be written using &, which is simpler and often preferable:

="He said, " & CHAR(34) & "Hello" & CHAR(34)

Why use &?

• Shorter and easier to read.
• No need for complex function calls.

Advanced Approach: Using VBA for Concatenation

For extensive text manipulation, VBA (Visual Basic for Applications) provides automation capabilities:

Function AddQuotes(text As String)
    AddQuotes = """" & text & """"
End Function

This enables dynamic text formatting with quotation marks without complex Excel formulas.

Real-World Applications

Creating CSV Files with Proper Formatting

If generating CSV data from Excel, enclosing text in quotes ensures correct structure:

=CONCATENATE(CHAR(34), A1, CHAR(34), ",", CHAR(34), B1, CHAR(34))

This converts:

Into:
"Apple","Banana" (standard CSV format).

Formatting SQL Queries Dynamically

Excel can format SQL INSERT statements dynamically to ensure textual values are correctly enclosed in quotes:

="INSERT INTO users (name) VALUES (" & CHAR(34) & A1 & CHAR(34) & ");"

This ensures outputs like:
INSERT INTO users (name) VALUES ("John Doe");

Generating JSON and Reports with Quotes

For structured formats like JSON, Excel ensures quotes are preserved:

="{""name"": " & CHAR(34) & A1 & CHAR(34) & "}"

Output:
{"name": "John Doe"}

Common Errors and Debugging Tips

1. Forgetting to Escape Quotes

❌ Incorrect:

=CONCATENATE("Hello "World"")  

✅ Correct:

=CONCATENATE("Hello ", CHAR(34), "World", CHAR(34))  

2. Mixing CONCATENATE and & Incorrectly

❌ Incorrect:

=CONCATENATE("Hello") & " World"

✅ Correct:

="Hello" & " World"

3. Misusing CHAR Function

Verify CHAR(34) is used correctly. Using CHAR(35) (the # symbol) instead of CHAR(34) will result in incorrect text formatting.

Conclusion

Mastering how to add quotes in Excel concatenation helps create structured outputs for CSV files, SQL queries, and formatted reports. Whether using CONCATENATE, CONCAT, TEXTJOIN, or CHAR(34), the right approach depends on your specific formatting needs. By applying these techniques, you can avoid text errors and streamline data processing.

Citations

• Microsoft. (n.d.). CONCATENATE function. Microsoft Support.
• Microsoft. (n.d.). CHAR function. Microsoft Support.