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

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