Filter with inverted commas

[Simon K]

Hi all - having a trouble with the following:

I want to conditionally format a field based on:

[if] length (filter ( mytextfield ; " !£") > 0 then format background yellow etc

But I also want to filter out inverted commas (" and ') from mytextfield

I am going round in circles looking at escaping with "" and the quote function...none of which seems to have the desired result

Any help grateuflly recd

thanks

Simon

for reference mytextfield is an e-mail address and I want to highlight the field if its got disallowed characters, what I have at the moment is

Length ( Filter ( Self ; " *,%<>!?[]{}£^&()/|~#") ) > 0

[Raybaudi]

Hi

quotes can be escaped like you said ( " ), single quote do not need an escape char.

So this has to work:

Length ( Filter ( Self ; " *,%<>!?[]{}£^&()/|~#'"") )

[Simon K]

Hi Daniele

Have pasted exactly as you put back into field so that I have

Length ( Filter ( Self ; " *,%<>!?[]{}£^&()/|~#'"") ) >0

It correctly formats the email address field except if it has a ' or " and then it doesn't apply the condidtional formatting

Any suggestions

Thanks

Simon

[comment]

Do you have smart quotes turned on (under File… > File Options > Text)?

[Raybaudi]

Hi

uncheck "Use smart quotes" into the File Options and re-paste that calc...

[Simon K]

Hi Daniele

Success - issue solved - many thanks

Simon

[Simon K]

Hi Michael

Thanks for responding - that was indeed the solution

Simon

[Raybaudi]

Have pasted exactly as you put back into field so that I have

Length ( Filter ( Self ; " *,%<>!?[]{}£^&()/|~#'"") ) >0

BTW: you didn't paste exactly my calc...

It was w/o the "> 0" part and will work the same way of your. :)

[comment]

I am not sure the solution is as simple as just turning them off - data entered while they were on may still contain them.

[Simon K]

Ok I have removed the ">0" and as you say it does work

but I have to admit to not understanding why?

- is the fact that it resolves to a number sufficient to make it true?

if the filter(self,chars)resulted in an empty string would the length of that string be 0 or null? and does that make any difference to a conditional test?

would like to know

cheers

simon

Edited November 30, 2008 by Guest

[Simon K]

Michael

Yes I just read as much -

Is there a way of doing a search and replace for the curly quotes?

there aren't too many records about 4000 or so - but its enough to want to automate a replacement routine if I can

thanks

Simon

[Simon K]

just found this from Fabrice Nordmann...in a similar thread

_str = Substitute ( string ; [ "“" ; """ ] ; [ "”" ; """ ] ; [ "" " ; """ ] ; [ "" " ; """ ] )

so will have a go...

[comment]

You can do this by showing all records and doing Replace Field Contents… with a calculated result, using the Substitute() function. Be sure to backup your data before trying this, though.

[comment]

_str = Substitute ( string ; [ "“" ; """ ] ; [ "”" ; """ ] ; [ "" " ; """ ] ; [ "" " ; """ ] )

That doesn't look right. IMHO, it needs to be:

Substitute ( string ; 

[ "“" ; """ ] ; 

[ "”" ; """ ] ; 

[ "‘" ; "'" ] ; 

[ "’" ; "'" ] 

)

I am not sure how much of that is visible here, but it's:

• left double quotation mark to quotation mark;

• right double quotation mark to quotation mark;

• left single quotation mark to apostrophe;

• right single quotation mark to apostrophe.

[Raybaudi]

...is the fact that it resolves to a number sufficient to make it true?

Yes, every not empty or not zero values are considered true.

[Raybaudi]

Isn't simpler to add those chars into the initial calculation ?

Length ( Filter ( Self ; " *[color:red]“”,[color:red]‘’%<>!?[]{}£^&()/|~#'"") )

Edited December 1, 2008 by Guest
Edit: comment's note

[comment]

Maybe - it depends. I would clean up the data after turning off smart quotes anyway.

In any case, the "smart" double quotation marks need to be escaped, too.

[Raybaudi]

In any case, the "smart" double quotation marks need to be escaped, too.

Thank you, I didn't know that.

[Simon K]

Many thanks - have also escaped the smart quotes now

cheers

Simon