How to Remove Quotation Marks and Line Feeds from Excel TEXTJOIN Output
Question details
The user needs to remove unexpected quotation marks, apostrophes, or line-feed characters generated when combining TEXTJOIN and FORMULATEXT functions.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using TEXTJOIN combined with FORMULATEXT to extract and concatenate formulas from multiple cells into a single string.
- Observed behavior
- The resulting combined string contains unwanted quotation marks, apostrophes, or line-feed characters instead of returning clean text.
Before modifying your formulas, identify whether the unwanted characters are standard quotation marks or hidden line breaks, as each requires a slightly different formula adjustment.
Use the SUBSTITUTE Function to Clean the Output
Wrapping your TEXTJOIN formula in a SUBSTITUTE function allows you to target and replace specific unwanted characters with empty strings.
The most effective way to remove artifacts generated by FORMULATEXT is to nest your main formula inside one or more SUBSTITUTE functions. This function searches for a specific character (like a quote or a line break) and replaces it with nothing.
To remove standard quotation marks, select your target cell and use the following formula structure: =SUBSTITUTE(TEXTJOIN("&",TRUE,FORMULATEXT(G1008:H1008)),"""","""). The four consecutive quotes represent a literal double quote in Excel.
If your original cells contain line breaks (Alt+Enter), remove them using CHAR(10): =SUBSTITUTE(TEXTJOIN("&",TRUE,FORMULATEXT(G1008:H1008)),CHAR(10),"").
To clean both characters at once, nest the formulas: =SUBSTITUTE(SUBSTITUTE(TEXTJOIN("&",TRUE,FORMULATEXT(G1008:H1008)),"""","""),CHAR(10),"").
Test for Workbook Corruption or Add-in Interference
If standard formulas behave unexpectedly or insert artifacts randomly across simple operations, the workbook may be corrupted or an add-in might be interfering.
Clean Up Complex Formulas with WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for advanced dynamic array functions like TEXTJOIN and text manipulation functions like SUBSTITUTE. You can easily manage complex formula combinations to format and clean your data without unexpected formatting errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your target formulas.
- 2. Select the target cell: Click on the cell where you want the combined formula output to appear.
- 3. Enter the nested formula: Type the nested SUBSTITUTE and TEXTJOIN formula to strip out quotation marks and line breaks.
- 4. Apply the calculation: Press Enter to execute the formula and view your perfectly formatted, clean text string.
Frequently Asked Questions
Why does FORMULATEXT add quotation marks?
When the FORMULATEXT function extracts formulas, it reads the exact string. If the original formula contains text strings naturally enclosed in quotes, they are pulled into the result, which TEXTJOIN then combines as literal text.
What is CHAR(10) in Excel formulas?
CHAR(10) represents the line feed (line break) character. It is often used to insert a new line within a cell or, when used in conjunction with the SUBSTITUTE function, to find and remove unwanted line breaks from a text string.
How do I represent a quotation mark inside an Excel formula?
To specify a literal double quotation mark in an Excel formula, you must use four consecutive quotation marks (""""). The outer quotes define the text string boundary, and the inner two represent the actual quotation mark character.
