Efficiently removing digits from Google Sheets cells

Other Questions Google Sheets
1

I’m trying to find a better method to get rid of all numbers in my Google Sheets. Right now I’m using a script that goes through each digit and some punctuation marks, then replaces them with blank spaces. But it’s really slow and feels clunky. Here’s what I’ve got:

function cleanNumbers() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var charsToRemove = '0123456789,|;.-';
  
  for (var i = 0; i < charsToRemove.length; i++) {
    sheet.getRange('A:A').createTextFinder(charsToRemove[i]).replaceAllWith('');
  }
}

Is there a faster or more elegant way to do this? Maybe using regex or some built-in function I’m not aware of? Any tips would be great!

2

Have you considered using a regular expression with the REGEXREPLACE function? It’s typically much faster than running scripts, especially for large datasets. You could try something like this in your sheet:

=REGEXREPLACE(A1, “[0-9,|;.-]”, “”)

This formula will remove all digits and the specified punctuation marks in one go. You can then drag it down to apply to all cells in the column. If you need to apply it to multiple columns, you can use ArrayFormula to make it even more efficient:

=ArrayFormula(REGEXREPLACE(A1:A, “[0-9,|;.-]”, “”))

This approach should be significantly faster and doesn’t require any custom scripts. Let me know if you need any clarification on implementing this!

3

hey benmoore, have u tried the SUBSTITUTE function? it’s pretty handy. u can chain em like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,“0”,“”),“1”,“”),“2”,“”)

and so on for all digits. its not as fancy as regex but gets the job done quick n easy. might be worth a shot!

4

I’ve faced similar challenges with data cleaning in Google Sheets. While the REGEXREPLACE function is great, I found that using a custom function can be more flexible and powerful for complex operations. Here’s a method that worked wonders for me:

function removeDigitsAndPunctuation(input) {
if (typeof input !== ‘string’) return input;
return input.replace(/[\d,|;.-]/g, ‘’);
}

You can then use this in your sheet like =removeDigitsAndPunctuation(A1). It’s blazingly fast and handles large datasets with ease. Plus, you can easily modify the regex to include or exclude specific characters as needed.

For bulk processing, you can combine this with ARRAYFORMULA:

=ARRAYFORMULA(removeDigitsAndPunctuation(A1:A))

This approach gives you the best of both worlds - the speed of built-in functions and the flexibility of custom code. It’s been a game-changer for my data cleaning workflows.