5 Ways to Find and Remove Blanks Spaces in Excel

In this video, you can learn 5 different techniques to find and remove spaces in Excel. Spaces can cause formula errors and frustration with data preparation and analysis. The five ways we explore to remove them are Find & Replace, the TRIM function, Power Query, Macros/VBA, and LEFT & LENGTH functions.

00:00 1. Find & Replace
02:53 2. The TRIM Function
04:30 3. Power Query
06:02 4. Macros and VBA
07:51 5. Trimming Other Characters

Link to the Free Excel Training on The Modern Power Tools:

Related Videos:

VLOOKUP Tutorial for Excel:

How to Create Macro Buttons in Excel Worksheets:

How to Create a Personal Macro Workbook in Excel and Why You Need It:

#MsExcel #ExcelCampus

[JordanKoevenig]

1 minute into your video and my life was saved... HERO

[darrylmorgan]

Great Tips, Have Had Many A Headache With Unseen Spaces..Thank You Jon :)

[mohammadeshaq]

You are a great teacher. I have learned a lot fom you.

[nadermounir]

Thank you Jon for your hard work and for sharing this. I try always not to miss any of your videos

[alicjjusz]

I didn't know why the first four ways didn't work so I've been removing those spaces one by one (sometimes even 150 And then I found this video with the CODE tip and it's working now. Thanks!

[wayneedmondson]

Hi Jon. Great video! Thanks for the excellent tips and tricks on how to locate and remove blank spaces in text. Thumbs up!!

[creatiw]

This was a good refresher on space hunting. I always have to google it as it doesn't happen that often. Thanks!

[axycllee]

Your tip is just in time. Thank you, Jon.

[denkydoo]

thanks jon.. more power to you and to your team!

[Savi-xrck]

This video saved my project - thank you!

[piyushjadhav]

Hello sir
Its showing error when i used trim function ref# what is it mean

[AnthonyCarrTV]

Thanks!! Saved me a TON of time! And aggravation!

[TheIrisVoice]

Hello Jon, thanks for your video. How to remove blank spaces in an excel file with huge data. It's not possible to remove one by one. Pls help with any video or suggestion. Thanks

[terencejohnson]

Non-breaking space in Windows is general across all Office applications and it was used mainly for keeping first name and last name for a person always on the same line. It also made sure that company names were expressed the same way.

[BenjaminTennysonjb]

Last formulae is great but what if we have different number of spaces for each cells. By doing -1 it will delete one space and how we can do for more than one..

[longmean]

really love your list search it useful on excel but we need on MS access too.

[excelrobot]

I kinda spaced out towards the end, but overall great content as usual!

[AnkitDasture]

I did use trim function but now from the new data I wish to remove the function so I can edit the data. If that could be mentioned it would be great !

[Tanquen]

With a large workbook and many sheets I need to simply find cells with strings that end with a blank space. It's crazy that Find and Replace works well for something like double space, lets you see all the strings first before you do a single replace or replace all but no regex or expressions to simple find a character at the end of a cell/string.

[warriorr]

I have Code 30 spaces that I can’t seem to remove. I cannot copy and paste it into the find and replace function. Any other suggestions?