Hamideh sends in an interesting question. He set up a really cool custom number format to prevent people from having to enter 4 dashes in a part number. However... now Find appears not to work. In Episode 673, I will show you the hidden setting to assist with the Find.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question that's sent in by Hamaday. Hamaday said.
Hey, I have this field that has to be in here like this ZT-3-7-962-012-0 Well he said it's a real pain to enter all those dashes.
So basically he went through and selected an area. Went to "Format Cells" and created a custom number format and in the custom number format He said look we're going to display this number with the ZT upfront.
So ZT- #-#-###-###-# Created a new custom number format, specifically for this field and it is pretty cool when you do that because now, you can enter a number for example 1 2 3 4 5 6 7 8 9 and it automatically displays it with all those dashes.
Very very cool the way that that works.
So let me just enter a few other numbers here.
Alright, but now the problem is when he went through and try to search, so we look for Ctrl F ZT-3-8-980-009-8 and do a "Find" It can't find what you're looking for, but clearly it's there.
Well the trick to make this work when you've used one of these custom number formats is you have to open the 'Options" tab and say instead of looking in "Formulas". We want to look in "Values".
Now we're going to do the exact same search.
"Find All" and there it finds it.
So any time that you've applied a custom number format even something as simple as cutting it back to two decimal places, like rounding off to two decimal places when you really have data that has more precision, and you try and find a number you need to go in and change the look in from "Formulas" to "Values".
Ofcourse doesn't help that it's hidden.
You have to hit that "Options" button in order to get to that particular feature.
Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question that's sent in by Hamaday. Hamaday said.
Hey, I have this field that has to be in here like this ZT-3-7-962-012-0 Well he said it's a real pain to enter all those dashes.
So basically he went through and selected an area. Went to "Format Cells" and created a custom number format and in the custom number format He said look we're going to display this number with the ZT upfront.
So ZT- #-#-###-###-# Created a new custom number format, specifically for this field and it is pretty cool when you do that because now, you can enter a number for example 1 2 3 4 5 6 7 8 9 and it automatically displays it with all those dashes.
Very very cool the way that that works.
So let me just enter a few other numbers here.
Alright, but now the problem is when he went through and try to search, so we look for Ctrl F ZT-3-8-980-009-8 and do a "Find" It can't find what you're looking for, but clearly it's there.
Well the trick to make this work when you've used one of these custom number formats is you have to open the 'Options" tab and say instead of looking in "Formulas". We want to look in "Values".
Now we're going to do the exact same search.
"Find All" and there it finds it.
So any time that you've applied a custom number format even something as simple as cutting it back to two decimal places, like rounding off to two decimal places when you really have data that has more precision, and you try and find a number you need to go in and change the look in from "Formulas" to "Values".
Ofcourse doesn't help that it's hidden.
You have to hit that "Options" button in order to get to that particular feature.
Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
