Named function📜 for Google Sheets. Trim Chars — Replace selected chars from a string

Max Makhrov
Aug 11, 2023

If you have some manually entered text in a column, and wish to clean it from unneeded chars, use this named function:


where the range [B4:B13] is your column with text values, [D4] is a cell with chars to replace.

Chars to replace is a textual value of all chars without a delimiter. In my example, it is: \:{}[]|;<>?`~💩.

To use a function:

  1. Copy my sample Spreadsheet
  2. Import the named function to your Spreadsheet
  3. Use as a regular formula.


The formula uses recursion:

=LET(v,B4:B13, c,D4, l,len(c), 
f_(SUBSTITUTE(str,mid(chrs,pos,1)," "),chrs,pos+1,f_))),

The recursion is in lines 2–4. It is used in line 5. It replaces the N-th char in a text with a space. The final result is trimmed to get rid of extra spaces.

Photo by Andrew Seaman on Unsplash



Max Makhrov

Google Sheets Developer, master of Online Accounting