When you join double quotes in formulas with text (prefix, suffix or both), Google Sheets treats it as a text. The formula uses the text ignoring the double quotes. If there are no double quotes then the text would normally be treated as a Named Ranges.
This poses a real problem when you really want to join double quotes using a formula to a text and let Google Sheets to treat it as text only.
I mean the total in the formula=total is treated as a Named Ranges because there are no double quotation marks around it. If there is no Named Range in that name, the formula would return “Unknown range name: ‘total'” error. On the other hand, the total in="total" is a text.
How to add double quotation marks to a plain text using a formula to avoid Google Sheets to treat the text as a named rage?
See the example below to learn how to join double quotes with text in Google Sheets.
Steps to Join Double Quotes with Text in Google Sheets
1. In a blank Google Sheets, in cell A1 enter the text Hello.
2. Use the below formula in cell B2.
=char(34)&A1&char(34)
This formula will prefix and suffix double quotations marks with the text Hello. The character double quotation mark is associated with the number 34. With CHAR function we can retrieve that text.
To concatenate double quotes with text as above, other than the ampersand, you can use the CONCATENATE function also. Here is that alternative to ampersand.
=concatenate(char(34),A1,char(34))
This way you can join double quotes with text in Google Sheets.
You have your own reason to concatenate double quotes with text. Here’s one practical example that shows the purpose of joining double quotes with text in Google Sheets.
Concatenate Double Quotes with Multiple Texts Using Formula
With the above ampersand or CONCATENATE you can join double quotes with multiple texts. But it would be a time taking effort.
When you want to concatenate double quotation marks with multiple texts, you can follow the below approach. I mean to enter the texts in a column and use one formula that can concatenate the texts and include double quotation marks.
I have the month names in Text from January to December in the range A1: A12. Here is the formula to join these month names and place surrounding double quotation marks, that also comma separated.
=ArrayFormula(join(",",char(34)&A1:A12&char(34)))
Join Double Quotes with Text in Google Sheets – Practical Use
In this example, I am going to use the above same month names in column A. The formula in cell B1 is almost the same but this time instead of the comma as the separator, I am using the semicolon.
=ArrayFormula(join(";",char(34)&A1:A12&char(34)))
So we have the month names in the chronological order in column A and the formula output in cell B1.
1. Right click on cell B1 and click “Copy”.
2. Again right click on cell B1 and select “Paste special”, “Paste values only”.
3. Select the range A1: A12, then click the menu Data > Randomise range. The month names are now not in the chronological order.
Similar: Formula to Sort By Month Name in Google Sheets.
4. Use the below custom SORT formula in cell F5.
=sort(A1:A12,match(A1:A12,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),TRUE)
This formula will sort the month names in column A, that is already randomized, back to chronological (custom) order.
You can learn how this formula works here in detail – Sort by Custom Order in Google Sheets.
In the above formula, I have used the text in cell B1 as the MATCH formula range. I hope this explains to you how to join double quotes with text in Google Sheets and use it in other formulas.
