Custom column that looks for special characters

[octalllink]

 I have a custom column where I'm trying to identify any of the following special characters in a specific field and returning True or False if it has any but I keep getting an error for one of the characters and I tried double double inverted commas but still getting the error.

The characters I need to find:

‘, ', ’, ', “, ", ”, ", |, ‘, “, $

This is my formula:

if Contains([CustomDataColumn], "‘", "'", "’", "'", "“", """, "”", """", "|", "‘", "“", "$") then "True" else "False"

It's the red underlined character I need to fix in this formula, please help?

[sevenhills · Accepted solution]

1. Contains does not exists. You may have to use List.Contains 

2. """" - four double quotes has to use if you are searching for double quotes

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclR41DAn8VHDXBCdBKKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [aa = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"aa", "CustomDataColumn"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if List.Contains({"'", "“", "”", """"},  [CustomDataColumn]) then true else false)
in
    #"Added Custom"

Hope this helps!

[Ritaf1983]

Hi @octalllink 
Remove """" from the formula 

you also don't need it logically because you have """ 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[octalllink]

I get this:

it's referring to character 34 ascii code