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:


=TRIM_CHARS(B4:B13,D4)

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.

Definition

The formula uses recursion:

=LET(v,B4:B13, c,D4, l,len(c), 
trimChars_,LAMBDA(str,chrs,pos,f_,
if(pos>len(chrs),str,
f_(SUBSTITUTE(str,mid(chrs,pos,1)," "),chrs,pos+1,f_))),
map(v,LAMBDA(vv,trim(trimChars_(vv,c,1,trimChars_)))))

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