Searching and reporting on special characters in a spreadsheet

[cjames728]

We have spreadsheets for which we have to check for special characters amongst the data. We have to check for the following

&,  &#,  /~*,  --,  <,  >,  ‘,  “,  ’,  Carriage Return,  Line Feed,  #. \. '

(Commas excluded)

I would like to report in the form of a error messages like:

AG18 - Special Character "&'" found　

M14 - Special Character "Line Feed" found　

T16 - Special Character "Line Feed" found

(We can't modify the spreadsheet without permission, we're tasked with finding things like this and reporting back)

The only way I can think of doing this is to have a filter for each character and for each column. Since our spreadsheets go to Column AH I can't imagine creating 422 filter tools for each tab. There's got to be a better tool for something like this.

For all my other checks I do a filter tool, send it to a "Report Text" tool which provides the error message and on it goes to a Union, and a render.....

[MarqueeCrew · Accepted solution]

@cjames728,

Think TRANSPOSE!  If your data is named with column headers, here's an example:

KEY|DATE|NAME

101|2017-12-19|Mark

202|2017-12-19|Mark & Gina

You can use a RECORDID tool and get a Row number and then TRANSPOSE the incoming data on ROW (as the key) and all other fields as data.  Now your output will be:

ROW|NAME|VALUE

1|KEY|101

1|DATE|2017-12-19

1|NAME|Mark

2|KEY|201

2|DATE|2017-12-19

2|NAME|Mark & Gina

Now you can use a FORMULA to create a new field via regular expressions to see what weird stuff you found.  FILTER on IsNull([STUFF]) and take the False output to see what was found where and in which rows.

Cheers,

Mark

[cjames728]

Absolutely Brilliant! That solved another (simpler) problem of how to report columns of missing required fields as well.

Many thanks!