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

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.

🔎 Video Insights:
- Understand why the TRIM function might fail to remove certain types of spaces.
- Learn about the non-breaking space (ASCII character code 160) often encountered in data from external sources.
- Master the SUBSTITUTE function to eliminate pesky non-breaking spaces.
- Discover how to use the VALUE function effectively when Excel fails to recognize numbers.

💡 Key Takeaways:
- Tackle common but tricky Excel challenges with confidence.
- Learn how to clean and format your data correctly, saving time and reducing errors.
- Enhance the reliability of your data analysis with these expert tips.

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

🚩Let’s connect on social:

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!

#excel

[eddywijaya]

Straight to the point. I've been searching this method for an hour. Thanks for the tricks!

[fcdanilo]

Hi, I'm from Brazil and watch everything I can from the channel. Thank you very much for the tips, your insights are very interesting and make the daily routine of those who use Excel as a work tool a lot easier. Good job, I wish you much success!

[vivekprabhakar]

You are awesome. Have done a few of your courses. The clarity in your explanations is of highest order.

[Taxita]

The best video. Solution to my problem when I convert PDF to excel. Thanks for sharing your knowledge.

[FaysalEasyExcel]

I know about Trim(). But the explanation you have given, has never seen before. thankyou.

[dorotheawright]

Leila, you are truly my HERO, I SO APPRECIATE YOU! This lesson has just allowed me to save 4hours of formatting. Thank you, thank you, thank you.

[spyroskonst]

Once again i'm speechless. The quality of explanation, including a workbook to test everything, is outstanding.

[alfayezamj]

Leila, you have no idea how long i have suffered from this spacing issue specially with numbers! you are going straight to heaven for making our life easier :) THANKS A LOT. i have been following your channel & becomeing a huge fan.

[williamreith]

I wish you'd made this video last week because I encountered exactly this problem on Sunday whilst reconciling expenses. Based on topic expertise, delivery, A/V quality, visuals, and narration, you are the best teacher on YouTube bar none. Furthermore, your lessons and courses yield excellent, professional results. I look forward to your Thursday morning installments and have watched your accounting videos since they began a couple of months ago. Great perspective. Thanks many times over.

[forestsunrise]

Dear Leila, thank you so much for your sharing your knowledge with us. Your videos have been helping me a lot with my new job. Please keep up the good work!

[indradevdubey]

Hey Leila, Thank you so much for this video it helped me alot to clean my data. Keep posting these kind of videos. Its a great belp for many people who are working on Excel. Thank you so much ❤️❣️

[BillHester]

Wow! Just Wow! Thank you for being such a marvellous human being Leila. After struggling all week to figure this out I could almost cry after listening to your wonderful voice explaining the way forward in such perfectly simple terms. Thank you!

[theengineonfire]

Char(160) did the trick. I know that I need to read more about it. Amazing. Thanks

[ariel-zight]

Leila, you rock! I've tried all the quick and easy ways of solving this issue and all failed. Finding the right character did the trick. Thanks a ton! Subscribed!

[romualdephraimbeda]

The way you save my life. Only GOD can pay you back. I was trying to fix that for months. I was reduce to find a VBA code until you come up with the LIGHTS. The only Excel Angel on earth :)

[kahhengyeong]

Another OMG from me! I thought I knew the trim function and nearly skipped this video. Not only i learnt so much more but also learnt not to skip your videos!!!

[ShahedFaisal]

I've had no idea about the non-breaking space until today. Thank you, Leila! This will save me a tonne of time.

[ajithindian]

Great.. this was exactly the answer which I was looking for. Explained in detail but simple and powerful way. Thanks a lot. May you be showered with all peace and prosperity.

[panzolinho]

Leila, you are a star. I have been struggling to find a youtube video that can fix this issue. You are an absolute star 🌟

[SasiKumar-pxwb]

The way you explore the excel is just awesome. Keep doing 👍🏼...