Have you ever come across a situation where you have copied and pasted data from some place else? So let’s say from an email or a web page into an Excel sheet and then you realize any formula you write that references this
range gives you errors? You dig deeper and do some research and you find out that one common cause for this type of error is probably because there’re
unwanted spaces there, like leading spaces or trailing spaces that were copied when you
brought the data to Excel. You find out that you can
use Excel’s TRIM function to remove these additional spaces. You do that and it works 90% of the time. Everything is great
until it stops to work. You copy a dataset to your Excel sheet, apply the Trim function
like you’ve always done but this time TRIM doesn’t work. (playful sad music) Let me show you what does. (upbeat music) So here in the second example, the TRIM function wasn’t able to remove the space. Why? The formula looks like it’s working. Let’s just double check what this part of the formula returns. I’m just going to highlight
it, press F9 to see what’s behind it. Notice that we still have that space even though I’m using the TRIM function. So I’m just going to press Escape to leave the formula. Now, TRIM can remove spaces but it can remove certain type of spaces. These are spaces that
have a character code or an ASCII character code of 32. There is another character code that represents a space
which is commonly referred to as a non-breaking space. The character code for that is 160. Now, these ASCII character codes are what’s used by computer programs to identify what character
you’re referring to. So for example, if I just add a space here and press Enter and I want
to see what character code is associated with the space, I can type the function
CODE, reference the cell, close the parentheses and press Enter. Character code of a regular space is 32. Let’s copy the character
space that we have here. Let’s paste it in here, drag down the CODE function. This time we get a character code of 160. This is a non-breaking space and it’s something you’re
going to come across if you’re copying data from Outlook or you’re bringing in data from other external systems into Excel. They might not all have
the character code 32. They might be 160. Now, there is an Excel formula that gives us the actual character back once we have the code. And that’s called the character formula. So if I reference 32 here, I actually get back a normal space and here a non-breaking space. We can see that if I copy
these and paste these as values and I go inside the cell, there is a space here and in the other one, we see a space. Now, to our eyes, both of
these look like regular spaces but to the computer’s eye, they’re not. The TRIM function removes these, it can’t remove this one. It would be great if Excel
had a TRIM all function and maybe that’s going
to come in the future which can trim out both
of these characters but for now, it doesn’t. So what we need to do is
use the SUBSTITUTE function, look for this non-breaking space and replace it with nothing. So instead of TRIM here, we’re going to use SUBSTITUTE. Now, the SUBSTITUTE
function has more arguments because first we need to reference the cell we want to transform, then old_text that we want to replace. So in this case, it’s
our non-breaking space. To make sure we get it right, we’re going to type in
the character code for it which was 160. Then the new_text that we
want to replace it with. Well, it’s nothing. So I’m just going to skip that argument. And press Enter and I get
back the correct description from the table. Now let’s send this down. It works for these, it
doesn’t work for this. What’s wrong here? Let’s highlight this, press F9 to see if we still have a space. We still have a space. So I substituted the non-breaking space but this one looks like it
has a regular space as well. So it would make sense to also use the TRIM function on top of the SUBSTITUTE function. Now let’s send this down. Everything works. But wait a second, what about the data? Can we just sum these up? Let’s use the Sum
function, it gives me zero. It doesn’t recognize
that these are numbers. Now, there seems to be a
non-breaking space before these. Usually, if you have a regular space and you have numbers in Excel cells, you can directly use the VALUE function on the cell to get the actual number back. But the VALUE function doesn’t work. What if I use the TRIM function and then the VALUE
function on top of that. Will this work? It doesn’t work. So here what I need to do is use the SUBSTITUTE function again. So let’s try that. Old_text is this one. New_text, what was the character code? 160. New_text is nothing so we can just leave this out. Close bracket, press Enter. This looks like a clean number but it’s actually not a number, right? Because it’s left aligned
and not right aligned. Excel still sees this as text. So now let’s put this
inside the VALUE function and now it looks like it’s a number. Drag these down. Now, let’s get the sum for these. So let’s just copy and
paste this and it works. So whenever you come across situations where your numbers are
not recognized as numbers, and you use the TRIM
function, it doesn’t work, use the VALUE function, it doesn’t work, remember to use the SUBSTITUTE function. Look for the character code 160 and replace it with nothing. Now, you might want to do all
of this in one single step instead of creating helper
cells like I’ve done here. Let’s try it here. We’re just going to combine all of this in one formula so just
to keep things simple, I’m just going to copy this formula, paste it here. Instead of referencing just C9, let’s reference the entire range here. This formula in the
new dynamic array Excel will spill and give us
actually what we see here. To make it not spill, we’re going to put it in the SUM function because SUM is just going
to return one result. Now if I press Enter,
it’s going to work for me because I have the new
dynamic array aware Excel. If you don’t have the
new version of Excel, instead of pressing enter, what you need to do is press
Control + Shift + Enter and yours will work just like mine does. I’m just going to drag the formula across. It works for this as well. So that’s how you can remove blank spaces from your Excel lists. I hope you find this video useful. If you did, don’t forget
to give it a thumbs up and don’t forget to
subscribe to this channel if you haven’t subscribed already so that you can get updates
when I put out new videos here. And I’ll see you in the next video. (lively music)


100 Comments

Ashwin Gowdillu · January 30, 2020 at 10:47 am

Thank you so much😊👍, I'm looking for this kind of videos.

Roberto Velicaz · January 30, 2020 at 11:01 am

Excellent as usual! Thanks Leila 😃

liam corcoran · January 30, 2020 at 11:01 am

This video couldn't of come at a better time:), I was just using the Trim function before I saw this. Just thinking could we use a custom function to combine both the TRIM and SUBSTITUTE function and store it in the personal.xlsb?

lg vdb · January 30, 2020 at 11:01 am

Hey Beautiful Genius,

From witch planet you are ?
You are an INCREDIBLE XL expert.
I love to watch your video's
Leo, Rotterdam

Kah Heng Yeong · January 30, 2020 at 11:04 am

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!!!

girishbabunaidu87 · January 30, 2020 at 11:10 am

Omg, this is amazing 👌 thank you,

Rahul Jauhari · January 30, 2020 at 11:25 am

I learned a lot about excel from you. Thanks

Kamalakkannan Kannan · January 30, 2020 at 11:26 am

Hi madam I have subscribed you YouTube channel . Every new post video I have seen same time .. but if possible I need Tamil language … Currently I am working .

Yulin Liu · January 30, 2020 at 11:40 am

Very useful tip. Thanks Leila!

Girish Poojari · January 30, 2020 at 11:53 am

Thank you i learned lot from your vedios 😍 👌

brahmam bvb · January 30, 2020 at 12:00 pm

Great great….thq

Laza Lazarevic · January 30, 2020 at 12:02 pm

Thanks for this!

vivek prabhakar · January 30, 2020 at 12:08 pm

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

Up4Excel · January 30, 2020 at 12:16 pm

Great tip on getting rid of those spaces that aren't really spaces Leila. Especially useful when taking downloads from the web or other systems. CHAR(160) is now committed to memory ❗😃💚

Wayne Edmondson · January 30, 2020 at 12:20 pm

Hi Leila.. great tips. Before watching this video, I was under the impression that CLEAN would solve for CHAR(160), but it does not. Your use of SUBSTITUTE along with TRIM and or VALUE is perfect. As always, learned something new today from your channel. Thanks for all the tips, tricks and insights that you so generously share with each new lesson. Thumbs up!
PS – Note that if you user Power Query, Transform=> Format=> Trim will remove the space in the original data created by CHAR(160) but Transform=> Format=> Clean will not.. interesting!

Faysal Easy Excel · January 30, 2020 at 12:23 pm

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

mostafa4321 · January 30, 2020 at 12:24 pm

Leila, you have just solved one of the peskiest problems I had and could never figure it out. Thank you however I usually change the cell from text to general or number and it usually does the trick unless I am missing something. 🙂

Isambardify · January 30, 2020 at 12:36 pm

It would be great if excel did trims for this and errant punctuation. There could be numbered options like for the subtotal function. It would also be nice to have a function to extract the first/second/ect word from a cell. I know this can be done nondynanically with text to collumns, or with lots of LEFT(LEN(FIND…. but it would be nice to have a single function I didn't have to look up every time.

Jean Aimé Faustin Tapsoba · January 30, 2020 at 12:47 pm

Thanks for this. It’s one of the common difficulties I had to deal with. Now I know a great solution. You’re amazing!!!

Moon Light · January 30, 2020 at 1:04 pm

Thank you Leila. It's great tips that I need for my data work

Romana Ahmad · January 30, 2020 at 1:17 pm

Amazing new information about space with ASCII code 160 that always annoyed me in the past.

Benjamin Allen · January 30, 2020 at 1:20 pm

Thanks for your video, and great solution. This behaviour has flummoxed me in the past!

Katerina · January 30, 2020 at 1:21 pm

Wunderschön Liebe Leila, wirklich nützlich, CHAR 160, zusammen mit TRIM gefällt mir sehr,
Viele liebe Grüsse 🤗 🌞

mtroanoke · January 30, 2020 at 1:28 pm

You can also use the CLEAN function in combination with TRIM to get rid of the non-printable characters that appear to be spaces. EDIT – after seeing another comment, I should clarify that it seems CLEAN will not work for CHAR(160) but it does work for many others.

André Arruda · January 30, 2020 at 1:38 pm

A couple of time ago, I was in trouble with char(160) when I was trying to run a VBA code I made based on strings. I passed like an entire hour trying to figure out what's going on there. Finally, I found the culprit and did the correction.

Majeed Panahi · January 30, 2020 at 1:40 pm

Excellent work Leila. Keep it up.

mtroanoke · January 30, 2020 at 1:41 pm

This is a nice reference article about cleaning data https://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19

Angie Cortes · January 30, 2020 at 1:41 pm

Hi! What about the CLEAN() function?

Syed Muntazir · January 30, 2020 at 1:47 pm

Wow Amazing… However there is a better way… Copy the space and replace it ( find and replace option) with normal space and then use TRIM formula. Hope this is helpful..

stingme · January 30, 2020 at 1:53 pm

You explain nicely. The first time I notice was in some conditional drop down menu creation. It was awesome.

stingme · January 30, 2020 at 1:54 pm

By the way.. u didn't mention about =CLEAN() function. I think the clean function might be able to get rid of it.

Pavan Varma · January 30, 2020 at 2:00 pm

Good info

Jl Roan · January 30, 2020 at 2:14 pm

been waiting for this for so long.. thanks!

mukund deshpande · January 30, 2020 at 2:31 pm

This vedio is amazing
…this technique will save my time a lot… thank you Laila

ACCOUNTING JOB WANTED ANY WHERE IN THE EARTH · January 30, 2020 at 2:55 pm

Essential Post I can't use those element because we born in 3rd world

Channel Films · January 30, 2020 at 2:57 pm

Follow Channel Films. Action Movies

Sasi Kumar · January 30, 2020 at 3:01 pm

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

ExcelIsFun · January 30, 2020 at 3:03 pm

GreatCHAR(160)VideoCHAR(160)Leila!!!!!

Sandeep Reddy · January 30, 2020 at 3:03 pm

Normally I use text to columns option to remove those extra spaces, but still this one really good one

I A · January 30, 2020 at 3:09 pm

Excellent

Rafael Robson · January 30, 2020 at 3:10 pm

Amazing! So simple, but so functional. Thanks.

Predrag Kojić · January 30, 2020 at 3:36 pm

Thanks Leila for good tricks. When will video be with advanced formulas?

Daniyal Ahmed · January 30, 2020 at 3:36 pm

I have learnt lot of Excel from your videos. You are amazing!

123rockstar2010 · January 30, 2020 at 3:43 pm

There's another solution. Beat up the person who sent those spaces in the workspace. Then use TRIM and SUBSTITUTE.

San Shres · January 30, 2020 at 3:48 pm

Great trick…I faced Char(160) while copying table data from email body… It messed up my mind for quite a long until I cracked it about 2 years ago using SUBSTITUTE() and TRIM()👍

Nadir · January 30, 2020 at 4:03 pm

Thank you for video!

Κωνσταντίνος Κουτσουνάδης · January 30, 2020 at 4:07 pm

OMG!! u r a life saver

jason stathom · January 30, 2020 at 4:23 pm

Oh lucky me… today morning only i faced same issue where trim didn't worked… fortunately i found this one when i really needed it .. thanks a lot mam

Omar H.L.L · January 30, 2020 at 4:28 pm

Bravo! :). Well done, always going the extra mile. Great tips

Raj Kumar · January 30, 2020 at 4:44 pm

It's help me in access to

Indradev Dubey · January 30, 2020 at 4:57 pm

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 ❤️❣️

Malik Hamaad · January 30, 2020 at 5:05 pm

Awsome.. i always have data to copy from email to excel and face such problem.
Thanks now my problem solved

John Steven Alburo · January 30, 2020 at 5:26 pm

In my case I find all spaces in both references and replace it with no space

dinesh kanth · January 30, 2020 at 5:28 pm

You are angle of excel madam, really….. Last week i got a data from external software & I faced the same V look up issue, I planned to type all look value again, you just saved me, THANK YOU

Manish Kumar · January 30, 2020 at 5:29 pm

Great tip!! Can you help me with how to use vlookup on merged cells?

Ontime Edu · January 30, 2020 at 5:35 pm

It's Advanced Trim function..Great learning today..Leila.! #Ontimeedu

Meet Sanghvi · January 30, 2020 at 5:37 pm

I had anothe very simple option i.e
Ctrl+h
The in find box " "(single space)
And press alt+a all space will be removed

KY Tricks & Vlogs · January 30, 2020 at 5:42 pm

Waw thanks

Remars Agustin · January 30, 2020 at 6:08 pm

the queen of formulas strikes again, thanks

Chip Turner · January 30, 2020 at 6:11 pm

Yet another useful everday tip! Thanks!

Vishwas Kadroli · January 30, 2020 at 6:45 pm

Awesome tip, thank you!

Andrey Mikhin · January 30, 2020 at 6:47 pm

Je vous remercie Madame Leila.
"Another brick in the WALL!!"

santiago cruz · January 30, 2020 at 6:50 pm

Hi Leila could you help me, I have a sheet whit data 1000×1000 this datas is to big, I need take for example the data a1 to j10 and obtain min value of this and put EN other sheet a1 , but the next k1to s10 I need the mini value en b2 and repeat for all sheet, can you give a tip for this thank.

Shadrack Awunyo · January 30, 2020 at 6:55 pm

Another "eish" moment video. Never seen before. Short, snappy and on point

Abizer Pardawala · January 30, 2020 at 7:06 pm

You are a blessing in disguise. Much love from dubai!

Romulus Milea · January 30, 2020 at 7:19 pm

Hello Leila,
One idea: in order to force Excel to treat a left aligned number as a number (right-aligned), instead of VALUE(SUBSTITUTE(A1,…), you can use 1*SUBSTITUTE(A1,…). This simply multiplies by 1 the result of SUBSTITUTE function. Of course, the result does not change by this multiplication, but Excel will accept it as a correct number. SUBSTITUTE function by its nature returns a TEXT value (as well as LEFT, MID, RIGHT, etc.). I suppose you are well aware of it. If not, well, I am glad that I added one drop of knowledge to your vast ocean :-D.

Shashi Kant · January 30, 2020 at 7:36 pm

You are awesome! Thank u!!👍👍

Affonso Duarte · January 30, 2020 at 7:39 pm

Leila crushed it again! Thanx for the tip!

Wessam Mohamed · January 30, 2020 at 7:55 pm

The Depreciation in excel Please

Conrado Hernán Villa Gil · January 30, 2020 at 8:22 pm

Leila thank you for the video. Leila it´s possible for this year 2020 videos about Data Science?.

Falcon mlik · January 30, 2020 at 8:33 pm

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

Dennis D · January 30, 2020 at 8:36 pm

Fantastic tip. Will save a lot of work in the future. Thank you.

ZAHID HUSSAIN · January 30, 2020 at 8:47 pm

Very, very excellent Leila! another thing apart from this if you don't mind "you are getting very cute with the passage of time". 🙂

Doug H · January 30, 2020 at 9:18 pm

Darn those Spaces…thanks for the video!

Michael Zuccaro · January 30, 2020 at 9:49 pm

Thank you for the introduction to the TRIM, Substitute and CODE functions!!! Another way to force a number corrupted with spaces into becoming true number, is to simply multiply the cell by one.

Michael Hearmon · January 30, 2020 at 10:01 pm

This is what I needed,, thanks

Vida · January 30, 2020 at 10:05 pm

Great overview of 'spaces'

ExactProBi · January 30, 2020 at 10:16 pm

we could use CODE function inside CHAR Function to let excel decide which Character code was in the cell?

Photoshop Automation · January 30, 2020 at 10:46 pm

cool

Martijn Fransen · January 30, 2020 at 10:49 pm

thanks Leila nice tip 👍

Juxtavarious · January 30, 2020 at 11:08 pm

I still prefer using my macro. But I have to select the columns in need of cleaning or it will go through the million-plus cells in the workbook.

brianxyz · January 31, 2020 at 12:02 am

Having Trim work the same way Text.Trim does in Power Query would be ideal. Implementing Text.TrimStart and Text.TrimEnd equivalents would also be nice. Until then I'll just continue to use Power Query for all of my data cleaning needs. Is there anything Power Query can't do?!

abin jacob · January 31, 2020 at 12:04 am

This was very helpful

tingting xiang · January 31, 2020 at 12:51 am

Thank you so much. It helps me on the trim failure which bothered me since long time ✌️☺️

DRSteele · January 31, 2020 at 1:15 am

Good insight, Leila! There are other stubborn characters that seem invisible and are hard to exorcise from cells. For example, go here (https://www.mrexcel.com/board/threads/filter-formula-return-only-certain-rows-from-table.1121383/#post-5409887) then copy and paste the data in Post#1 to Excel. Try to convert the text to actual numbers – very frustrating! After much experimentation (which I did after your video) I discovered that we have to substitute UNICHAR(8203) for a blank and use VALUE to get the job done. Some web pages and browsers just have weird characters.

Anginson J. Cervantes Irigoyen · January 31, 2020 at 4:19 am

Thnxs Leila I am performing a vlookup where the lookup value and the table array both need to be trimmed in order to find a match, I do not want to change the database, Is it possible to trim the array value in a vlookup formula Examples of the concept are below:
I want to put the trim on the array lookup value, in addition to the lookup value:
VLOOKUP(TRIM(E13),trim(data),1,0)
or
VLOOKUP(TRIM(E13),trim('1053'!$H$13:$H$233),1,0)
The normal formula without trimming the lookup array:
VLOOKUP(TRIM(E13),data,1,0)

Amit Sinha · January 31, 2020 at 4:32 am

Thanks for a good learning.. I would like to request you to make a video on Delimit function.. We can also remove extra spaces from it and Manny more.. Your video on Delimit function (Alt+D E) will clear my doubts.. Thanks
Amit sinha

Eduardo Jimenez · January 31, 2020 at 4:34 am

Hello Leila When working in a table with rows totals summed horizontally, and need to be summed vertically as well, would be useful to have Excel shows if the H and V sum match. Don't know if already we have such a function. Regards!

Rajat Jain · January 31, 2020 at 5:57 am

Nice one

Spyros Konst · January 31, 2020 at 8:17 am

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

Nasko Next · January 31, 2020 at 9:11 am

You have to do same series for google spreadsheets too

Arxad · January 31, 2020 at 9:33 am

Amazing….. you really saved many many hours of my life. Every tutorial is worth watching

Romulus Milea · January 31, 2020 at 10:46 am

Hello again,

One more quick tip, I use it whenever needed: when you write a formula, using only one function (e.g. =SUM(A1:A100)), you do not need to write the closing parenthesis, Excel is always there for you, and for all of us, and it will reach a helping hand and close that parenthesis for you. This could save you 1 second, every time you use it. What would be your opinion ?

Craig Esson · January 31, 2020 at 10:50 am

Ka-rakter?

Ashok Kumar · January 31, 2020 at 1:58 pm

Hey Laila – its a superb trick and will gonna help me alot. 🙂

Deependra Gupta · January 31, 2020 at 2:26 pm

I use copy space before character n replace with blank n job done

Sami Odeh · January 31, 2020 at 2:39 pm

Ms. Leila
First of all am a huge fan of your learning video so thanks for the effort

Second kindly note that some times I come across a sum number which comes out of a group of numbers and I don't not know which numbers lead to the sum number of the final number that I have, the question is there an Excel formula that helps me figure this out problem

I'd be thankful if you respond to my question

Thank you

Thierry FORTIN · January 31, 2020 at 3:07 pm

Olla!

Tu es toujours au top Leila, quel bonheur de suivre tes vidéos sur Udemy, en plus de celles de youtube bien sûr !!!!

Chandan Mhatre · January 31, 2020 at 3:30 pm

I always wonder, from where you get these brilliant ideas… Really amazing…Now I can boast in front of my colleagues n bosses 😂😂😂

Urman Taqi · January 31, 2020 at 3:55 pm

Thank you for superb videos . Best wishes

Leave a Reply

Your email address will not be published. Required fields are marked *