Problem
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.
Solution
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 Advanced Filter:
To completely filter out an entire row for any record that contains any number 0 through 9, simply use the following as an Advanced Filter
:
!MATCHES(#Sheet!Column;'.*[0-9]')
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: .*[0-9]
If true, we can set the value to blank, if false, we keep the column value.
IF(MATCHES(#Sheet!Column;'.*[0-9]');'';#Sheet!Column)
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.
Comments
0 comments
Please sign in to leave a comment.