The SUBSTITUTE function has a major flaw - it can’t tell opening quotes from closing quotes. Your example needs the first quote to become « and the second to become », but SUBSTITUTE treats them all the same way. I hit this exact issue with multilingual docs. You need REGEX instead. Try this: =REGEXREPLACE(B3,“"([^"]*)"”,“«$1»”) This finds complete quoted phrases and swaps the quotes correctly. It grabs the opening quote, captures what’s inside, finds the closing quote, then rebuilds everything with Greek quotes. Way better than basic substitution since it keeps the pairing logic intact. I’ve used this for months and it handles even messy sentences with multiple quotes.
You’re trying to replace standard double quotes (" ") with Greek quotes (« ») in a Google Sheet. You want a formula that efficiently handles this replacement across multiple cells, correctly distinguishing between opening and closing quotes. You’ve tried using SUBSTITUTE, but it’s insufficient for this task because it doesn’t differentiate between opening and closing quotes.
Understanding the “Why” (The Root Cause):
The SUBSTITUTE function in Google Sheets performs simple text replacements. It doesn’t understand the context of your quotes; it just replaces all occurrences of a specific string with another. Because Greek quotes have distinct opening («) and closing (») characters, a simple substitution won’t work reliably. You need a function that can intelligently identify and replace these quotes based on their position within the text. Regular expressions (regex) provide this capability.
Step-by-Step Guide:
Use REGEXREPLACE for Contextual Replacement: The most effective solution is to use the REGEXREPLACE function. This function allows you to use regular expressions to find and replace text patterns within a string. The following formula will correctly replace your double quotes with Greek quotes:
=REGEXREPLACE(B3,"\"([^\"])*\"","«$1»")
Explanation:
B3: This refers to the cell containing the text you want to modify (replace B3 with the actual cell reference if needed).
\"([^\"])*\": This is the regular expression. Let’s break it down:
\": Matches a double quote (the opening quote). Note that we escape the quote character with a backslash (\).
([^\"])*: This is a capturing group. It matches any character that is not a double quote ([^\" ]), zero or more times (*). This captures the text inside the quotes.
\": Matches the closing double quote.
«$1»: This is the replacement string. $1 refers to the captured group (the text inside the quotes). The formula replaces the matched pattern with the opening Greek quote («), the captured text, and the closing Greek quote (»).
Apply the Formula to Multiple Cells: Once you have the formula working in a single cell, you can copy it down to other cells to apply the same replacement to other text strings.
Verify Results: Check that the formula correctly replaced all double quotes with the corresponding Greek quotes, ensuring opening and closing quotes are accurately matched.
Common Pitfalls & What to Check Next:
Unpaired Quotes: If your text has unpaired quotes (e.g., a single opening quote without a closing quote), the REGEXREPLACE formula might not function as expected. Consider adding error handling or pre-processing steps to address such cases.
Other Quote Types: If you have single quotes or other types of quotes in your text, you may need to adjust the regular expression to handle those cases.
Text Encoding: Ensure that your Google Sheet is using an encoding that supports Greek characters.
Formula Errors: Double-check for any typos in your formula or incorrect cell references. The backslashes in the regular expression are crucial for proper escaping.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!
you can actually use something like =SUBSTITUTE(SUBSTITUTE(B3,“"”,“«”),“"”,“»”) to swap out regular quotes. just make sure you adjust it based on the actual quotes in your text.
The substitute approach works but gets messy with mixed opening and closing quotes. Greek quotes use « for opening and » for closing, so you’ve got to handle them separately.
I hit this same issue localizing content for European markets - thousands of rows with quoted text that needed proper Greek quotation marks.
What saved me was setting up automation that detects opening vs closing quotes based on context. Manual formulas turn into a nightmare with complex text and multiple quote pairs.
My automation handles the logic automatically - reads the text, spots quote patterns, converts them properly without complex nested formulas. Plus it processes entire columns at once instead of cell by cell.
For your situation, just set up simple automation that grabs your Google Sheets data, processes the text conversion, and writes it back. Way cleaner than building this logic in spreadsheet formulas.
Check out Latenode for this kind of text processing automation: https://latenode.com
Had this exact problem processing invoices with quoted product descriptions for a Greek client. The REGEX solution works, but here’s something simpler that’s been rock solid for me: =SUBSTITUTE(SUBSTITUTE(B3,CHAR(34),“«”,1),CHAR(34),“»”,1). First occurrence becomes opening Greek quotes, second becomes closing ones. Multiple quote pairs? Just nest more SUBSTITUTE functions. CHAR(34) beats typing quotes directly - no escaping headaches. Ran hundreds of cells through this without issues. One catch: your quotes need to be properly paired or it’ll break.
honestly, just use find & replace. hit Ctrl+H, paste the " in the find box and « in replace, then hit replace all. do it again for closing quotes with ». takes 10 seconds and no formula headaches.
Understanding the “Why” (The Root Cause):
Step-by-Step Guide:
Common Pitfalls & What to Check Next:
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!