Named function📜 for Google Sheets. Trim Chars — Replace selected chars from a string
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:
- Copy my sample Spreadsheet
- Import the named function to your Spreadsheet
- 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.