How to Remove Non-Numeric Characters in Google Sheets?
Clean Non-Numeric Characters is a Google Sheets function that replaces all non-numeric characters in a cell with an empty string, effectively removing them.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling with unwanted symbols in your data? Use REGEXREPLACE to easily clean your text cells and retain only numeric values.
The Formula
"Remove all non-numeric characters or unwanted symbols from a text cell using REGEXREPLACE in Google Sheets"
=REGEXREPLACE(A1, "[^0-9]", "")This formula replaces all non-numeric characters in a cell with an empty string, effectively removing them.
Step-by-Step Breakdown
- A1 is the cell reference containing the text you want to clean.
- REGEXREPLACE is the function used to perform the replacement.
- "[^0-9]" is the regular expression that matches any character that is not a digit.
- "" is the replacement string, which is empty, meaning matched characters are removed.
Edge Cases & Warnings
- Input with only non-numeric characters will return an empty string.
- Input with mixed characters will only return the numeric parts.
- Leading zeros in numeric strings will be preserved.
Examples
"abc123!@#"
123"456xyz789"
456789Frequently Asked Questions
Can I modify the regex to keep certain symbols?
Yes, you can adjust the regex pattern to include specific characters.
What if my cell is empty?
The formula will return an empty string if the input cell is empty.
Does this formula work with decimal numbers?
No, this formula only retains whole numbers; decimals will be removed.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.