How to Remove Extra Spaces using Power Query in Excel

This is video about removing the unnecessary white spaces from a data set using Power Query in Excel.
How to create an Custom Function in Power Query is also explained in this video.
🔵 Download my free ebook on Excel Macros eepurl.com/gpT0RD
🟢 Read my articles on Excel xlncad.com/
🟣 Join my course on Excel Tables www.udemy.com/course/tables-i...
Disclosure: Some of the links are Affiliate Links! If you buy something through one of those links, you won't pay a penny more, but we will get a small commission, which helps keep the lights on. Thank you for your support!
#RemoveExtraSpace #PowerQuery #MLanguage

[@chaswa68]

Thank you so much for publishing this. I managed to add a slight adjustment that helped me with my problem: to remove only multiple spaces and not single ones, the also to replace with a delimiter so I could then split the columns. It's not perfect but if anyone else needed this then here is that changed code. The delimiter is # in this case. I also put two spaces in the x function instead of one.
(text as text)=>
let
x = Text.Split(text," "),
y = List.Select(x,each _""),
z = Text.Combine(y,"#")
in
z

[@ParsaH]

Very helpful! Works perfectly! Thank you very much!

[@krishnakishoremalisetty3003]

This is brillient Ajay!! thanks for sharing.

[@damdelima]

Thanks man! Works like a charm!

[@r_faelpereira]

Pretty good solution. Thanks for sharing

[@curious888cute]

It’s working with your method, awesome!! Thanks very much.

[@mnowako]

Awesome! Thank you!

[@GosCee]

Thank you for this, Ajay. How could I remove extra spaces from a column in a nested table before expanding the table?

[@peterpferdproductions1043]

Very helpfull for me, thank you very much! I tried with search for two spaces or more and replace with nothing like in MS Word "SPACE{2;}", but this did not work.

[@neto8733]

Amazing

[@IMAS7512]

Dear how to remove spaces in number colomn

[@paramjeet975singh3]

sir is this possible with trim function

[@AjayAnandXLnCAD]

You are absolutely right. But the Power Query method is more Dynamic than any other method. Please watch the video till end and let me know what you think.

[@paramjeet975singh3]

@@AjayAnandXLnCAD ok i will watch

[@bijoomalloor1647]

Is there any short cut method for this instead of write program ..? Substitute funtion will remove all spaces including single space between words.. Trim will not remove space in between words.. So, is there any other function which will remove only extra spaces from a sentence..?

[@kcomarcos]

replace " " for "" without the quotes would work