Vineela is testing if two cells are the same with =IF(A2=B2. Both cells look exactly the same, but Excel is saying they don't match! How to find why they don't match.
Table of Contents
(0:00) Cells look alike
(0:20) =A2=B2
(0:33) Leading spaces
(0:45) F2 for trailing spaces
(1:20) Length & each character
(2:18) MID & ROW
(2:50) Five new books
(2:59) Get ASCII CODE
(3:20) Non-Breaking Space 160
(3:52) Formula to Fix
(4:50) Wrap-up
Table of Contents
(0:00) Cells look alike
(0:20) =A2=B2
(0:33) Leading spaces
(0:45) F2 for trailing spaces
(1:20) Length & each character
(2:18) MID & ROW
(2:50) Five new books
(2:59) Get ASCII CODE
(3:20) Non-Breaking Space 160
(3:52) Formula to Fix
(4:50) Wrap-up
Transcript of the video:
So these two cells look the same, but why don't they match?
Today's question from Vineela.
Has an Excel sheet, comparing two cells, if this equals that.
And visibly the cells look the same, but the formula's saying they're not the same. How can you figure out why they're not the same?
So here I have two columns of data and are they the same?
So equal A2 equals B2. And it's TRUE there.
But as I double click to copy this down, none of the others match, even though most of them look exactly the same.
I can see right here, right away, I can see that there's a leading space there in B5. That's the problem.
But here, just comparing it letter by letter, it really seems to be the same.
So F2 is our friend here. Select the cell, press F2.
And look for that flashing insertion point. Right there, there's a space after the H.
Here, there's not.
Down here, F2, no space after the H, F2 here, several spaces after the H.
All right. But then the most frustrating one is down here.
No spaces after that H and no spaces after that H, those are the ones that drive me crazy.
When I run into someone in a seminar who tells me about this, I'm like, "Ah, just send me the file".
And when I have them send me the file, here's what I actually do.
First thing I want to know is I want to know the length.
What's the length of the thing over in A, 38 characters.
What's the length of the thing in B, 38 characters.
Okay. Then I want to compare character by character.
And today with dynamic arrays, this is easier. So the MID of A$6, which character did I want?
I want all 38 characters. So the SEQUENCE of 38 for a length of one.
And that very nicely gives me letter by letter. And I can just copy this over here.
And because I only put a single dollar sign before the six, not before the A, the A is allowed to move.
All right now, there's a good chance that you don't have those new array functions. Here's the old way that we had to do it.
So equal MID of A$6, and then which character we want?
We want ROW of A1.
That's a very clever way of writing the number one for a length of one. Copy that formula.
And we have to copy it down 38 rows.
And then we want to know character by character do these match. Is that equal to that?
And this step right here will tell us exactly where we have a problem.
It's right there, there's the problem.
So, that space doesn't match that space, doesn't match that space, doesn't match. All right, next step.
Hey, check out all these new books, click on that (i) in the top right hand corner.
All five of these just came out this summer. They're all available in print.
All great books.
As I add two more characters, and this time I use a function called a code. Give me the ASCII code behind that character.
Copy this down.
And this will tell us exactly what the problem is.
So right here, the original data over here has spaces, real spaces.
I pressed the space bar on the computer and the right data has non-breaking spaces. Character 160 is a non-breaking space.
That means that this data at one point came from a website.
On websites, especially if they're trying to line data up, they will use Character 160 spaces, , to make sure that the browser doesn't eliminate extra spaces.
And those Character 160s are problematic, because Excel doesn't see them as the same.
So here's my fix.
If we have a whole bunch of these, hundreds or, thousands of these, and it's not practical to press F2 on each one, then I write this formula.
So I take the SUBSTITUTE of A2 changing every Character 160 to just a regular space.
That's quote, the space bar on your keyboard. And then I send that into the TRIM.
TRIM getting rid of leading spaces, trailing spaces and interior repeated spaces.
Copy that down. Copy it over.
And then the match here, does this match that? Double click and copy it down.
It does. Perfect.
Okay, so now that I know that these are correct, I'm going to just take them.
Right-click on the edge drag over here.
Copy Here As Values Only, and now all of these matches are true. I'm always doing this dance alone.
I say, "Just send me the file and let me figure it out".
It's kind of embarrassing that we have to go to this length to figure out what the problem is, but I've done this hundreds of times. This is the way to figure out what the problem is.
If you like these videos, please down below like subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Vineela for setting that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's question from Vineela.
Has an Excel sheet, comparing two cells, if this equals that.
And visibly the cells look the same, but the formula's saying they're not the same. How can you figure out why they're not the same?
So here I have two columns of data and are they the same?
So equal A2 equals B2. And it's TRUE there.
But as I double click to copy this down, none of the others match, even though most of them look exactly the same.
I can see right here, right away, I can see that there's a leading space there in B5. That's the problem.
But here, just comparing it letter by letter, it really seems to be the same.
So F2 is our friend here. Select the cell, press F2.
And look for that flashing insertion point. Right there, there's a space after the H.
Here, there's not.
Down here, F2, no space after the H, F2 here, several spaces after the H.
All right. But then the most frustrating one is down here.
No spaces after that H and no spaces after that H, those are the ones that drive me crazy.
When I run into someone in a seminar who tells me about this, I'm like, "Ah, just send me the file".
And when I have them send me the file, here's what I actually do.
First thing I want to know is I want to know the length.
What's the length of the thing over in A, 38 characters.
What's the length of the thing in B, 38 characters.
Okay. Then I want to compare character by character.
And today with dynamic arrays, this is easier. So the MID of A$6, which character did I want?
I want all 38 characters. So the SEQUENCE of 38 for a length of one.
And that very nicely gives me letter by letter. And I can just copy this over here.
And because I only put a single dollar sign before the six, not before the A, the A is allowed to move.
All right now, there's a good chance that you don't have those new array functions. Here's the old way that we had to do it.
So equal MID of A$6, and then which character we want?
We want ROW of A1.
That's a very clever way of writing the number one for a length of one. Copy that formula.
And we have to copy it down 38 rows.
And then we want to know character by character do these match. Is that equal to that?
And this step right here will tell us exactly where we have a problem.
It's right there, there's the problem.
So, that space doesn't match that space, doesn't match that space, doesn't match. All right, next step.
Hey, check out all these new books, click on that (i) in the top right hand corner.
All five of these just came out this summer. They're all available in print.
All great books.
As I add two more characters, and this time I use a function called a code. Give me the ASCII code behind that character.
Copy this down.
And this will tell us exactly what the problem is.
So right here, the original data over here has spaces, real spaces.
I pressed the space bar on the computer and the right data has non-breaking spaces. Character 160 is a non-breaking space.
That means that this data at one point came from a website.
On websites, especially if they're trying to line data up, they will use Character 160 spaces, , to make sure that the browser doesn't eliminate extra spaces.
And those Character 160s are problematic, because Excel doesn't see them as the same.
So here's my fix.
If we have a whole bunch of these, hundreds or, thousands of these, and it's not practical to press F2 on each one, then I write this formula.
So I take the SUBSTITUTE of A2 changing every Character 160 to just a regular space.
That's quote, the space bar on your keyboard. And then I send that into the TRIM.
TRIM getting rid of leading spaces, trailing spaces and interior repeated spaces.
Copy that down. Copy it over.
And then the match here, does this match that? Double click and copy it down.
It does. Perfect.
Okay, so now that I know that these are correct, I'm going to just take them.
Right-click on the edge drag over here.
Copy Here As Values Only, and now all of these matches are true. I'm always doing this dance alone.
I say, "Just send me the file and let me figure it out".
It's kind of embarrassing that we have to go to this length to figure out what the problem is, but I've done this hundreds of times. This is the way to figure out what the problem is.
If you like these videos, please down below like subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Vineela for setting that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
