How Clean Text Data

[Galat]

Hi, I need to pull all the single words out of the sentence.
I mean I should have just been words without numbers or any other symbol!
I use the following formula:
Table.AddColumn (# "Changed Type", "Custom", each Text.Remove ([name], {"!", "@", "#", "$", "%", "^", "& "," * "," (",") "," - "," _ "," + "," = "," {","} "," [","] "," | ", "\", "/", "~", "` ","? ",": ",". "," <","> "," 0 "," 1 "," 2 "," 3 "," 4 "," 5 "," 6 "," 7 "," 8 "," 9 "}))

The problem is I can't quite clear everything and there are many more symbols left between the words. Of course, it is not possible to manually search for any icon and delete it.

Does anyone know a way to deal with this problem?

Raw Data 

[Anonymous]

try

TextEncoding.Utf8
TextEncoding.Unicode

=D

[Anonymous]

For another situation I had built a function that removes "non-standard" characters from a text. I think it could be for you.

let
  fnCngNotStndCharsToDef = (str_in as text, stndChrs as list, optional defChar as text) => 
    Text.Combine(
        List.Transform(
            Splitter.SplitTextByRepeatedLengths(1)(str_in), 
            each Record.FieldOrDefault(Record.FromList(stndChrs, stndChrs), _, defChar)
          )
      )
in
  fnCngNotStndCharsToDef

an example of use:

PS

Actually the function has a slightly more general extension, being able to replace non-standard characters with a chosen default character.
If this is omitted, the result is to remove unwanted characters.

[mussaenda · Accepted solution]

Hi @Galat,

I searched and found this:

Text.Select([name],{"a".."z","A".."Z","0".."9"," "})

By the way, I found that on this thread:

https://community.powerbi.com/t5/Desktop/Removing-special-characters-and-symbols-from-a-column/td-p/... 

Hope this helps!