Cell Appears Blank or Empty but Formula picking it up as populated

[Masond3]

Team, 

I am a little confused on a scenario i am encountering. 

I have a formula that evaluates if a cell is populated or not. However i have scenario below,  where to me the "Value" field  appears blank / empty but the formula is staying that its populated. 

ideally as the "Value" Column is blank it should return "Fail" and not "Pass"

Formula : 
if isnull([Value]) or isempty([Value]) then 'Fail 'else 'Pass' endif

Regards
Masond3

[Masond3]

@danilang  That makes sense - but how do you then reverse it to highlight that the the long dash is the char that would fail the validation ? 

[danilang]

@Masond3 

Take out the Not operator "^" at the front and it will replace all the valid characters leaving only the dash [A-Z|a-z|0-9|... vs [^A-Z|a-z|0-9|...

REGEX_Replace([name], '[A-Z|a-z|0-9|À|È|Ì}Ò|Ù|à|è|ì|ò|ù|Á|É|Í|Ó|Ú|Ý|á|é|í|ó|ú|ý|Â|Ê|Î|Ô|Û|â|ê|î|ô|û|Ã|Ñ|Õ|ã|ñ|õ|Ä|Ë|Ï|Ö|Ü|Ÿ|ä|ë|ï|ö|ü|Ÿ|¡|¿|ç|Ç|Œ|œ|ß|Ø|ø|Å|å|Æ|æ|Þ|þ|Ð|ð|!||"|#|$|%|&|\(|\)|\*|\+|,|\-|\.|\/|\:|;|\<|\=|\>|\?\|@|\[|\\|\]|\^|_|`|\{|\||\}|\~|¢|£|¤|¥|¦|§|¨|©|ª|«|¬|­|®|¯|°|±|²|³|´|µ|¶|·|¸|¹|º|»|¼|½| |¾|\}]', "")

Dan

[Masond3]

@danilang  That's very useful indeed. I have included the additional item in Bold, as i have embedded new lines.

'[A-Z|a-z|0-9|À|È|Ì}Ò|Ù|à|è|ì|ò|ù|Á|É|Í|Ó|Ú|Ý|á|é|í|ó|ú|ý|Â|Ê|Î|Ô|Û|â|ê|î|ô|û|Ã|Ñ|Õ|ã|ñ|õ|Ä|Ë|Ï|Ö|Ü|Ÿ|ä|ë|ï|ö|ü|Ÿ|¡|¿|ç|Ç|Œ|œ|ß|Ø|ø|Å|å|Æ|æ|Þ|þ|Ð|ð|!||"|#|$|%|&|\(|\)|\*|\+|,|\-|\.|\/|\:|;|\<|\=|\>|\?\|@|\[|\\|\]|\^|_|`|\{|\||\}|\~|¢|£|¤|¥|¦|§|¨|©|ª|«|¬|­|®|¯|°|±|²|³|´|µ|¶|·|¸|¹|º|»|¼|½| |¾|\}|\s+]', "")

I have created a formula for each name and address fields which puts the invalid chars into designated columns. 

I then add a recordid to the dataset then transpose the data (on all the new columns)  and then have another formula to evaluate if the field is empty / null. However I am seeing record identifying as “pass” but the field appears blank

Referring to your earlier post I am trying to execute on these steps. But this is where I am stuck (probably me begin stupid ) 

.

1. Add a RecordID to the dataset.
2. Split the value column to rows using a Regex tools set to tokenize using "." as the regex expression.  This will put every character into its own record
3. use CharToInt([Values]) to get the ascii code for each character.  
4. the unique list of these codes will tell you the extra characters that you need to include in your regex filter.  Use the \x0000 syntax to include any non-printable characters 

I will try and provide more scenarios tomorrow to articulate the problem , but if you run it for that one test record you should see what I mean