Here we’ve got both leading and trailing space characters in the data.There are 5 spaces added onto the start.Looks ok, but there are actually 5 spaces added onto the end.This is just a control, to make sure when we apply our fix we don’t break something that’s correct. It’s just “ Hello World!” over and over again but with various extra spaces thrown in. Our Example Data for the Postįor this post, we’ll look at a simple set of data. In this post, we’ll look at a variety of methods to remove these unwanted extra space characters from our data. Now that we know the problem, how can we fix it?

It may even have multiple spaces between words that shouldn’t be there.

The data may have space characters at the start or at the end. The first time this happened to me, I spent ages trying to find the problem only to discover some of the lookup data had extra space characters attached at the end! But for some unknown reason, a handful of results are errors.

You can remove any “hidden” character that you like with this method.Īnd here’s the workflow: Remove any special characters.knwf (19.It’s something that has happened to me in the past many times.įor some reason, your VLOOKUP‘s are returning an #N/A error and you have no idea why.Įverything looks good and the formula should be working. The hex code for the degree character is 0xb0, which is in the first 2 records:Īs you can see, the degree character has been removed. As a test, I will remove the degree character “ °”, so 69.0 ☌ for example will become 69.0 C. Nevertheless, I can still do a demo with the data that you have. So, it looks like Excel is converting them. 0x20 in hex is basically a space - I used to build drivers 20 years ago and dealt with these hex values a lot, so these hex values bring back some memories. We can see the hex value “20” at the end of Row3 and Row7. Row3 and Row7 look to have a “hidden” character at the end. This is what I read from your Excel file:Īnd to check if there are any hidden characters, I do a quick join of “XXX” before and after the values in result, and I get: I think that’s probably because Excel converts them. Hi, just to demonstrate what I was explaining.įirst, though, your Excel file does not really contains “hidden” characters.

Finding hidden characters in excel