I was given a file to import. All the Spot Names have quotes: "This Summer".
How I remove the quotes? When I tried substitute I can't enter the ".
I assume there is some tag I need to enter.
Hi user28271 ,
Looks as though the quote chars that you are trying to remove might not be ordinary double quote chars, but instead, they are possibly of the "curly" variety.
If this happens to be the case, the following calc might be more along the lines of what you intended:
Substitute( SpotName ; [ Char( 8220 ) ; "" ] ; [ Char( 8221 ) ; "" ] )
That said, I agree with Michael / comment , that a better approach might be one which preserves any quote chars which are inside (part of) the string content, e.g. the suggestion of using the Middle function, instead.
HTH & sincerely,
You need to escape the quotes when using them literally:
Substitute ( YourField ; "\"" ; "" )
Note that this will remove all quotes, not just the enclosing ones. Perhaps it would be better to use the Middle() function for this?
Thanks....but I must be doing something wrong.
Here is a screen grab. The field in YELLOW is the SpotName Cleaned
You my new hero Mr Ash
Using Middle to remove the first and last character.
Middle ( SpotName ; 2 ; Length ( SpotName ) - 2 )
Another unstable pedantic way
Evaluate ( SpotName )
since smart quote also treated as quote in FM calculation...
This would return unwanted result if the filed have ¶, ", line break etc.
If you want middle but might have text that doesn't start or end in " or “ ”
Let ( [
a = Trim( text ) ; b = List( 8221 ; 8220 ; 34 ) ; l = ValueCount( FilterValues( b ; Code( Left ( a ; 1 )))) ; r = ValueCount( FilterValues( b ; Code( Right ( a ; 1 )))) ] ;
Middle( a ; l + 1 ; Length ( a ) -l -r )
Retrieving data ...