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
If you want to transform any value that contains any number 0 through 9 instead:
You want to use the IF function in conjunction with the MATCHES function.
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.
Please sign in to leave a comment.