How to remove nonessential characters such as punctuation, numbers, web addresses

[arthastic]

Hi!

Help me please with removing nonessential characters from text column.

I use Replace Values function to remove this characters:

= Table.ReplaceValue(#"Removed Columns1",",","",Replacer.ReplaceText,{"message"})

How to modify this formula to replace several characters in one step. Or is it ony other way to remove special symbols.

Regards!

[arthastic]

Thank you guys for your help!

Regards!

[Sean]

@arthastic

You can also use the User Interface to

Trim - Remove leading and trailing whitespaces from each cell in the selected columns!

Clean - Remove non-printable characters in the selected columns!

[Anwesha_Sharma]

"Clean" worked for me - thanks 🙂

[MarcelBeug]

@Sean I'm afraid you are mixing up "non-essential" with "non-printable".

This solution will not return "MarcelBeug" from my example.

[Sean]

@MarcelBeug

Transform tab - Forrmat button - Clean => read the Tooltip!

[MarcelBeug]

@Sean

Turn audio on => watch the Video!

[Sean]

@MarcelBeug

I never said Clean would be a solution!

Before @arthastic accepted your answer as a solution he gave smpoure the heads up as if Trim would be enough!

So I started preparing my answer before yours was marked as the solution!

Just wanted to show the Trim can be accessed through the User Interface

And because Clean is right underneath Trim I decided to include it - thats it - nothing else!

EDIT: The Audio is amazing!

[Greg_Deckler]

Use Text.Trim and specify the optional trimChars parameter:

https://msdn.microsoft.com/en-us/library/mt260494.aspx

[MarcelBeug · Accepted solution]

@Greg_Deckler: Text.Trim only removes characters at the start or at the end of a text.

My suggestion would be to convert the text to a list of characters, remove the unwanted characters and return the result back to a text.

= Text.Combine(List.RemoveItems(Text.ToList("M,,,a.r;;;celB.;e.u.;g"),Text.ToList(",.;")))