Processes a text string to enable clean copying and pasting into text fields without quotations
=LET(
text, copy,
a, SUBSTITUTE(text, CHAR(10), CHAR(13)),
b, SUBSTITUTE(a, CHAR(160), " "),
c, REGEXREPLACE(b, "[’‘]", "'"),
d, REGEXREPLACE(c, "[“”]", CHAR(34)),
e, SUBSTITUTE(d, "…", "..."),
f, REGEXREPLACE(e, "[–—]", "—"),
g, SUBSTITUTE(f, "---", "—"),
h, REGEXREPLACE(g, "(^|[[:space:]])--([[:space:]]|$)", "$1—$2"),
i, REGEXREPLACE(h, "(^|[[:space:]\(\[])-(\d)", "$1−$2"),
j, REGEXREPLACE(i, "([^\r ]) {2,}", "$1 "),
k, SUBSTITUTE(j, "- - -", "---"),
k
)Source: Eunomiac
This function prepares a cell's text for clean copying and pasting into plain text fields. Primarily, it stops Google Sheets from wrapping copied cells in quotation marks by replacing line feeds (CHAR(10)) with carriage returns (CHAR(13)). It also normalizes common typographic characters: non-breaking spaces become regular spaces, curly quotation marks become straight ones, ellipsis characters become three periods, en dashes and triple hyphens become em dashes, a hyphen directly before a number becomes a true minus sign, and runs of multiple spaces are collapsed to one. For example, if A2 contains "Wait… see page 3 -- now" then =CLEAN_COPY(A2) returns "Wait... see page 3 — now". Because the formula is built with LET, each cleanup step is a separate named line, which makes it easy to customize by removing steps you don't want or adding your own.
Use the inputs below to create CLEAN_COPY as a reusable custom function in Google Sheets.
Learn how to add custom functions to Google SheetsCLEAN_COPY
Processes a text string to enable clean copying and pasting into text fields without quotations
=LET( text, copy, a, SUBSTITUTE(text, CHAR(10), CHAR(13)), b, SUBSTITUTE(a, CHAR(160), " "), c, REGEXREPLACE(b, "[’‘]", "'"), d, REGEXREPLACE(c, "[“”]", CHAR(34)), e, SUBSTITUTE(d, "…", "..."), f, REGEXREPLACE(e, "[–—]", "—"), g, SUBSTITUTE(f, "---", "—"), h, REGEXREPLACE(g, "(^|[[:space:]])--([[:space:]]|$)", "$1—$2"), i, REGEXREPLACE(h, "(^|[[:space:]\(\[])-(\d)", "$1−$2"), j, REGEXREPLACE(i, "([^\r ]) {2,}", "$1 "), k, SUBSTITUTE(j, "- - -", "---"), k )
copy
The text to clean up before copying
A2
Other functions in the same category: Text Manipulation & Formatting
Concatenates multiple ranges or arrays and removes duplicates
Counts the total number of words within a cell or range of cells
Extracts text between two delimiters
Return uppercase initials from a name or phrase
Returns the Levenshtein edit distance between two text values
Lowercases text, trims it, removes punctuation, and collapses whitespace
Named functions enable the creation of custom, reusable formulas that mimic built-in functions, streamlining calculations and data manipulations. These functions simplify complex formulas, making spreadsheets more readable and less prone to errors. By encapsulating intricate logic within a single function call, they enhance consistency across your data. Utilizing named functions reduces the need to write lengthy formulas repeatedly, thereby improving workflow efficiency and productivity in data analysis and management.
Download and import — fastest way to add the function but does not include argument descriptions and examples
Follow these simple steps to download and import a function into your spreadsheet. This method is the quickest and easiest way to add the named function to your Google Sheets document, but will not include the argument description and examples.
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.
Copy and paste — copy and paste each property one at a time
Follow these simple steps to integrate a custom named function into your spreadsheet:
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.