The Find command seems to work perfectly with text, but always has problems with finding numeric values. It is possible to find numbers with the Find command in Excel, but you have to know some fairly arcane steps. Episode 522 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question about using edit find, when you're trying to find numbers.
You know think about Excel, so many of our cells contain values or formulas or numbers of one way or another and it seems that edit find doesn't always seem to want to work.
So, if I use edit find and say that I'm looking for perhaps 800, you'll see that.
There's two spots in the range that contain an 800 here in Q3 and then also the total for Row 2.
So, if I ask Excel to find next it will find the value in C6, but when I hit find next, again.
It says there's no other 800 there.
This is really frustrating because you and I can see that there's an 800 right here in E2.
What's going on?
Well, it turns out that we have to use the options button and within the options button, we have to say that we want to look in, not formulas but in values.
So, in the default state where it's looking in formulas.
It would have to have a formula that said like equal A 2 times 800 in order to be found, just by changing this two values.
We can say find next and there it finds a little one over the total.
Find next, find next, find next, it'll jump back and forth between the two of them okay!
So, far so good.
It sounds like things are working.
But let's see if we can find maybe this 2,500 or 2,600 down here in the total.
So, now if I search for 2500 and click find next.
Microsoft Excel cannot find the data, you're searching for.
Well, you know this is again...
This is ridiculous because we can see it right here it turns out that for whatever bizarre reason.
I can't figure out, why Microsoft would decide to do this?
Because our data is formatted to show the thousands separator the comma.
That we actually have to search for 2 comma 500 and click find next and then all of a sudden the numeric values will work.
Now, this is really strange couple things, you have to do.
If you're going to search your number.
So, I am gonna use edit find.
First of all hit the options tab, and say that you're looking in values instead of formulas and then secondly you have to figure out, what number format is applied to the range and make sure that you put in, two decimals or the comma or the dollar sign.
Even if you're formatted as currency.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question about using edit find, when you're trying to find numbers.
You know think about Excel, so many of our cells contain values or formulas or numbers of one way or another and it seems that edit find doesn't always seem to want to work.
So, if I use edit find and say that I'm looking for perhaps 800, you'll see that.
There's two spots in the range that contain an 800 here in Q3 and then also the total for Row 2.
So, if I ask Excel to find next it will find the value in C6, but when I hit find next, again.
It says there's no other 800 there.
This is really frustrating because you and I can see that there's an 800 right here in E2.
What's going on?
Well, it turns out that we have to use the options button and within the options button, we have to say that we want to look in, not formulas but in values.
So, in the default state where it's looking in formulas.
It would have to have a formula that said like equal A 2 times 800 in order to be found, just by changing this two values.
We can say find next and there it finds a little one over the total.
Find next, find next, find next, it'll jump back and forth between the two of them okay!
So, far so good.
It sounds like things are working.
But let's see if we can find maybe this 2,500 or 2,600 down here in the total.
So, now if I search for 2500 and click find next.
Microsoft Excel cannot find the data, you're searching for.
Well, you know this is again...
This is ridiculous because we can see it right here it turns out that for whatever bizarre reason.
I can't figure out, why Microsoft would decide to do this?
Because our data is formatted to show the thousands separator the comma.
That we actually have to search for 2 comma 500 and click find next and then all of a sudden the numeric values will work.
Now, this is really strange couple things, you have to do.
If you're going to search your number.
So, I am gonna use edit find.
First of all hit the options tab, and say that you're looking in values instead of formulas and then secondly you have to figure out, what number format is applied to the range and make sure that you put in, two decimals or the comma or the dollar sign.
Even if you're formatted as currency.
Hey! Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
