Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?)

30 January 2020 Excel Advanced Formulas & Features

Stubborn spaces can become a headache in Excel. You'll come across extra spaces when you copy data from your email or import data from another system. Each cell value ends up having trailing or leading spaces. Sometimes they can be removed easily with the TRIM function but sometimes TRIM doesn't work! In this video I'll show you how you can TRIM ALL Spaces - also the stubborn ones. 

TRIM can only remove spaces with an ASCII character code 32. But there is another character code that represents a space. It's commonly referred to as a Non-Breaking Space and its ASCII character code is 160. Such spaces commonly occur when we copy text from Outlook or a website to Excel. With the Excel function CODE() we can identify which character code we are dealing with.

To remove a non-breaking space in Excel we can use the SUBSTITUTE function. With this function we can look for character code 160 in a cell and replace it with a regular space.

For numbers that are not recognized as numbers by Excel we can use the VALUE function. If the number contains non-breaking spaces use it in combination with the SUBSTITUTE function.

00:00 What To Do When VLOOKUP Does NOT Work
01:01 What to Do When TRIM Does Not Remove Spaces
03:36 Use SUBSTITUTE to Remove Spaces Between Words
04:49 What To Do When Excel Does Not Recognize Numbers
07:20 Wrap Up

⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-trim-all-spaces

Check out other Excel tips and tricks in this playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A

★ My Online Excel Courses ► https://www.xelplus.com/courses/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel #Excel #LeilaGharani