How To Filter Out Any String That Contains Numeric Values
In order to sanitize a specific string column of data, all values that contain numerics need to be handled. For example,
abcdef is a valid value, while
abc123 is an invalid value.
We can approach this solution in two different ways depending on the ideal way to handle the data for the situation.
The most simple solution is using an
To completely filter out an entire row for any record that contains any number 0 through 9, simply use the following as an
You'll use MATCHES to set up a regex that can identify any entries with numbers in it:
If true, we can set the value to blank, if false, we keep the column value.
These statements break down as:
- IF #Sheet!Column has any numbers 0-9 within
- THEN transform to NULL
- ELSE keep the original value.
You can change the values into anything here, but for the example you used
'' as the second argument, which produces null. You could instead insert a specific string to make rows that failed this test easily identifiable.