Look Who it Was Random video

Excel - Excel Tutorial: Adding Dashes Inside of Digits with Custom Number Format - Episode 673

331 views · Published 19 February 2009 · 2:27 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Adding dashes inside of digits in Excel using a custom number format.

Welcome back to the MrExcel netcast! In today's episode, we have a question from one of our viewers, Hamaday. He's been struggling with entering a specific number format, ZT-3-7-962-012-0, which can be quite tedious with all those dashes. But fear not, because I have a solution for you.

First, select the cells where you want to enter this number format. Then, go to "Format Cells" and create a custom number format. In this case, we want to display the number with "ZT" upfront, followed by a dash, then three numbers, another dash, and so on. So the custom number format would be ZT- #-#-###-###-#. This is a great way to save time and make your data look more organized.

Now, here's the cool part. When you enter a number, let's say 123456789, it automatically displays it in the format we specified earlier. No more manually adding dashes! But, there's a catch. When you try to search for a specific number using the "Find" function, it won't work. That's because the search is set to look in "Formulas" by default.

To make it work, you need to open the "Options" tab and change the search to look in "Values" instead of "Formulas". Now, when you do the same search, it will find the number you're looking for. This trick applies not just to custom number formats, but also to other formatting changes like rounding off decimals. Just remember to change the search to "Values" if you're having trouble finding a specific number.

One thing to note is that the "Options" button is hidden by default, so you'll need to click on it to access this feature. But don't worry, it's a small price to pay for the convenience of using custom number formats. Thank you for tuning in to this netcast from MrExcel. Don't forget to hit that like button and subscribe for more helpful tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/



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.

Table of Contents:
(00:00) Introduction
(00:10) Question from Hamaday
(00:20) Formatting the field
(00:37) Solution: Change search settings
(00:51) Creating a custom number format
(01:07) Issue with hidden data
(01:17) Troubleshooting the search
(01:36) Importance of changing search settings
(02:00) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Custom number format
Custom number format and searching
Display numbers with dashes
Find in Formulas
Find in Values
Format Cells
Hidden values
MrExcel netcast next time
Options tab
Search using Ctrl F

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152228/

More from this channel