 Hello everyone and welcome back to another video if this is your first time checking out one of our videos on the channel Then please do subscribe and hit that Bell notification button As you'll then be notified of all our future videos as they come out in each video We try and pick a topic or particular function and really dive in with examples to help you understand What it's doing and where it can be valued to your work and also if you do enjoy this video This is goes for everyone else. If you did enjoy this video, please do hit it I hit that like button It's causing greatly appreciated by us because it not only helps let you to algorithm It also helps show me the content you'd like to see more of in today's video We're going to be looking at how to get rid of any unwanted spaces and firstly I start with an apology that please do try ignore the awful formatting and very basic layout of this In this file we've got here, but we're going to be jumping around with some of the information on this sheet But it's the real core point We're going to be looking at in columns a to see with the data that is I really don't want to strap the attention from So in this scenario, we've got two sets of data both look exactly the same We've got column jet or columns J to K and we've also got columns M to N And you can see we've got an ID and some sales data and our ID is a location ID And we want to return the location name from our search table in column F and G So we'll jump straight in we're going to copy this first bit of data and Oh, I've pushed the wrong button there ignore that Copy this and we're going to paste it over into our desired columns of A to C and This is just trying to replicate a scenario where you're copying data either from a website or maybe another Microsoft application like word or even outlook So you can see we've copied our data across there and we can see the total sales To put in our location name. All we're going to do is do a VLOOKUP And if you're not familiar with how to do the VLOOKUP function Do check out the other videos we have available on the channel as there are many Going in more depth into the VLOOKUP function to show you how to use it as we won't be going into too much depth in this video So once I've entered my VLOOKUP, you can see that I now have a location What makes gives our data a bit more meaning and sense so we can now see that Tokyo had the highest sales and that New York had the lower sales to give two very obvious and not meaningful piece of information there So with our second piece of data now, this is being rigged to give us Maybe as an example of where data is maybe not formatted correctly from a potentially bad source And it's not necessarily going to be a bad source. It's just that formatting can change when you're moving across applications So we're going to copy this data here. So we just copy exactly the same way we did and paste it underneath the previous data And all we now need to do is just copy this formula down so we can see the locations But we can see there's a problem and it's the VLOOKUP is giving us an error and the error for that without having to Sort of dive in and do too much digging is because we already know is there is a space at the end of the ID So when we're looking for the idea of a one zero one zero, what's the first one here for New York? Obviously this value doesn't contain all the actual ID in the search table doesn't contain a space Therefore, that's what's causing the problem And you can just see if I manually remove that space from here and enter then the VLOOKUP can now perform and return its actual location But we'll just put that space back in there So we need to make an update to our existing VLOOKUP formula to deal with this space all we need to do is go at the front here and I'm just going to enter the function of trim and Do that around here as well So you can see what are now the brackets running inside that value and hit enter and it's now worked and given us That desired value what trim does and again like the example with VLOOKUP There are other videos on our channel if you want to look at more into the trim function how it works You can check them out there, but in a nutshell trim works by looking at your value So for us, it's this one here a one zero one zero and it says okay if there's any spaces at the beginning of the The string or the end of it all I'm going to do is just remove them and exactly what it does So it literally gets rid of any spaces and either side of the value obviously if there's a space in the middle It wouldn't remove it but any space is either side It will remove those spaces and then give us the the number as desired And you can see that works for New York and if I just to drag it above Into our existing ones obviously it impacts them or it doesn't impact them at all because there's no spaces for it to need to extract But yeah, so it's not a one of those formulas where if a space isn't present it will cause an error It will literally only activate should we say if those spaces are there So I can just now copy this down and I should know it hasn't sorted to those examples out So we can now look to see why are they not not working? So I'll jump into this one in row 11 Go up into the former bar here and I can see that there is a space there But obviously trim is not working with this particular type of space So if you didn't know there are potentially should we say two different types of spaces? There's a normal space So that the one we're able to resolve there by using the trim function and there's also another space Called a non-breaking space So for us as just looking at them it looks no different We can it just looks like a space but Excel obviously Recognizes these two different spaces as different references and therefore we need to feed into our formula a solution for Excel to what to do when one of those particular spaces comes up So what I'll go quickly do is add a box over here and then what you can see is I will replicate by using the formula This particular these two different types of spaces So all I need to do is do equal C-H-O-R and open brackets and the first time well The first one is going to be number 32. So chapter 32 and enter and Character third or character with number 32. So this is our normal space So Excel will recognize a normal spelling space with this reference of 32 And I can just copy that down to save me writing the formula again very lazy and For the second one, I'm going to enter the number one six zero So this second one Excel recognizes as a non-breaking space with the reference of one six zero so if I was to then just copy these and Let's do Copy and we'll do paste values Just get rid of those references You can now see if I just put this border back on if I go into each one So this first one you can clearly see there is a space there Perfect, so I escaped out of that and the second one again, you'll see there is another space there So they're getting they both look exactly identical to us, but when I use a final formula here of code So the first one I put in there of C-H-O-R allows me to Create that scenario and obviously using code allows me to now reference what appears like a blank cell To understand what the code references So obviously see that first one is 32 and that second one is 160 so we now know that a Normal space is going to be number 32 and a non-breaking space is going to be code reference 160 so Trim will help us deal with number 32 But we need another update to our formula to help us deal with the code of 160 when we encounter the non-breaking spaces So what we can do is we'll go back over to our formula here And the first thing I want to do is for the time being is just remove This trim function and that reference. So let's delete that out I'm now going to use the formula function of substitute so just type in substitute and What this does is it basically you input what value you want the formula to look for and Then you then provide the value you want it to substitute that found the value for So we first need to say take in our ID So the text being obviously a reference to where our text is We'll then go common and say, okay, what is the old text we want to find to be substituted So we're going to put char 160 Because we know that is a non-breaking space is code So where the character that 160 code is referenced all we want to do is We don't want to put any new text so we can just we want it just to be blank So all I'm going to do is having entered the comma just put no more there and just close my brackets and enter So what you can see what happened is as I now pull this formula down all those values the first four Obviously no spaces are present so it's not going to do anything But for those two where we had so Mumbai and Paris the two values and I've been pulled through Where one of those non-breaking spaces was appearing it's now going to find that space replace it or basically remove it replace it with nothing and Therefore our formula will now work So obviously as a result of doing that substitute removing trim it's caused errors to these ones with the normal spaces So lastly we can just round this off by now just entering the trim function around our just entered substitute function like so So what will happen now is Our substitute function will work. It will say okay for our ID Is there any non-breaking spaces if so replace them with nothing and then as a secondary measure It will then say okay having done that substitute function. Is there any more spaces ie normal spaces around our new ID if there is remove them else obviously there's nothing to be done and then that will then give us our Perfect value ready to be searched or looked up in that search table Hit enter we can see we've got New York there So we know there was no problems with that one at all Copy that all the way down by a double click and you can now see that we have a solution to deal with both the Normal and the non-breaking spaces So we hope that was obviously insightful and gave you a really good Formula to use with your data Appreciate the data we looked at today is is there's not very much of it So you might just want to go and just manually remove those spaces But as you can appreciate if you have a lot more data where you need to apply this to be look up to or If you're doing this on maybe a recurring basis, whether it's daily or weekly You don't maybe want to rely on a manual process because that could either give you An error because if you forget to do it And alternatively if you're doing it on a regular basis that soon adds up a lot of time that you spend doing this manually so this is the formula that I would suggest you can use and This obviously doesn't have to just be used review look up You could use it of any other function where you're trying to do a search in this scenario and That will give you a solution to use So we hope you enjoyed that video if you did as mentioned at the beginning Please do give the video a like obviously it helps that YouTube algorithm Or most importantly and helps other people find this content and it also gives great insight to myself to know What content you're enjoying and what you may be like to see more of if you haven't already Again, do subscribe to the channel hit that bell notification button We bring out one video a week or lots more content to come out So obviously by doing that you'll be notified when more content comes out onto the channel So lastly, thank you very much for watching and we'll see you in the next video Before you go don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlist so you can see these categorized together So make sure you check those out and get all those useful information and obviously as always don't forget to subscribe and hit that bell notification button