Entering Structured Labels Custom Formatting In #Excel

You have a series of model or part numbers that are very similar in structure, and you don’t want to have to type them all in manually. Is there an easy way? Sure! Check this out:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Let’s say you have 50 part numbers to enter and they are all structured the same with initials for your company name (BBR) followed by the warehouse number (22) and a 4 digit code. So the numbers would be structured like this: BBR-22-xxxx. The easiest way to manage that is to create a custom formatting to lay out the numbers as you’d like, without re-typing all the repetitive information.

First, highlight the area of the sheet that you want to format:

cf1

Right click on the highlighted area and select Format Cells…. then Custom. You can then type into the box under “Type:” the format that you want to create:

cf2

For this specific project, there are a few rules to remember:
– put into quotes what you want to appear as text
– use the # symbol as a placeholder for a significant digit, as long as there is a digit to display. For example, if you had a format ##.## and entered 5.2, it would just display 5.2.
– use a 0 as a placeholder for a significant digit where you want to insert a 0 if you don’t enter a number. For example, if you had a format 00.00 and entered 5.2, it would display 05.20, putting a 0 in the place where no digit was entered.

Now, for the project that we have, we want to set up a format like this: BBR-22-xxxx. Based on the rules above, create the custom format “BBR-22-“0000.

cf3

Now, when you type any number into the formatted cells (i.e. 1, 2, 3, 4, etc), here is what you’ll see:

cf4

If instead you used this format: “BBR-22-“####, you would get the following results:

cf5

Notice how each custom format you create is saved to the bottom of the list for reuse:

cf6

There are so many other options and features available with Custom Formatting such as colors, how to format negative numbers, etc., but this will at least give you a taste of some of the capabilities available in Excel. So, format away!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply