Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using a Custom Format to Add Dashes.

Using a Custom Format to Add Dashes

by Allen Wyatt
(last updated April 25, 2015)

1

Justin has part numbers that he routinely uses in a worksheet, such as 660501C016971. He would like to apply a custom format to the cell and have the part number automatically displayed with dashes at the proper location, such as 6605-01-C01-6971.

Unfortunately this cannot be done with a custom format. Why? Because custom formats are for the display of numbers, not text. There is one text format, designated by an "at sign" (@), but that is it; there are no others and no others can be defined.

Since custom formats cannot be used, one is left to figure out a workaround. One way to do it is to examine your part numbers and see if the text portion of the number can be removed and the part number still be usable. For instance, Justin's number is 660501C016971. If the format for the part number always calls for the letter C at the same point in the part number (and no other possible letters there), then you could simply delete the C and be left with the number 660501016971. Since it is a number, you can develop a custom format for it that includes dashes in the proper places and the letter C in the proper place. The custom format would look like this:

0000-00-C00-0000

With the format applied to a cell that contains the number 660501016971, you would end up with a correctly formatted part number displayed: 6605-01-C01-6971. This approach does have drawbacks, however. The biggest drawback is that if you ever want to export the part numbers to another program, perhaps as a CSV file, what ends up exporting is the original number without the formatting or the letter C.

Another workaround is to use a formula to display the part numbers in the format you desire. You could enter them into a cell without dashes, and then use the formula to add the dashes at the appropriate places. When creating reports, then, you would simply hide the column that contains the part numbers without the dashes. Here's a formula that will work, provided the part number without dashes is in cell A1:

=LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & MID(A1,7,3) & "-" & RIGHT(A1,4)

If you work with the part numbers quite a bit, you might want a way to both add and remove the dashes easily. The best way to do this is with a macro. You can develop a macro that will allow you to add and remove the dashes from a part number in a selected range of cells. The following is an example of such a macro.

Sub DashesIn()
    DoDashes ("In")
End Sub
Sub DashesOut()
    DoDashes ("Out")
End Sub
Private Sub DoDashes(What As String)
    Dim c As Range
    Dim J As Integer

    For Each c In Selection.Cells
        If c.Value <> "" Then
            J = InStr(c.Value, "-")
            Select Case What
                Case "Out"
                    While J > 0
                        c.Value = Left(c.Value, J - 1) & _
                          Mid(c.Value, J + 1, Len(c.Value))
                        J = InStr(c.Value, "-")
                    Wend
                Case "In"
                    If J = 0 Then
                        c.Value = _
                          Left(c.Value, 4) & "-" & _
                          Mid(c.Value, 5, 2) & "-" & _
                          Mid(c.Value, 7, 3) & "-" & _
                          Right(c.Value, 4)
                    End If
            End Select
        End If
    Next c
End Sub

Note that there are actually three macros in this listing. The first (DashesIn) adds dashes to a part number, while the second (DashesOut) removes them. Simply select the cells containing the part numbers and then run the macro that will perform the operation you want done.

Both DashesIn and DashesOut call the common routine, DoDashes, to actually do the work. The macro examines all the cells in the selection and then performs whatever operation needs to be done on the contents of those cells.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9394) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Using a Custom Format to Add Dashes.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Mixing Column Formats On a Page

Want to switch the number of columns used for your text, in the middle of a page? You can do this very easily by following ...

Discover More

Zooming In On Your Worksheet

If you have trouble seeing the information presented in a worksheet, you can use Excel's zooming capabilities to ease the ...

Discover More

Wrapping Text Around a Text Box or Frame

Text boxes and frames can be used for all sorts of information and objects in a document. You can wrap text around the text ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Notation for Thousands and Millions

When working with very large numbers in a worksheet, you may want the numbers to appear in a shortened notation, with an ...

Discover More

Understanding Date and Time Formatting Codes

Want to apply a custom format to your dates and times? To do it effectively you need to understand the custom formatting ...

Discover More

Getting Rid of Leading Zeros in a Number Format

Excel, by default, displays numbers with a leading zero, if they are less than 1. Here's how you can get rid of those leading ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2016-02-24 09:10:30

John DZamba

I am looking for a format that will list numbers with dashes in their logical order when sorted like this-

316
317
2515
2516
2516-1
2516-2
2516-2-1
2516-2-2
2517
3048-09CC03
3059-2
3060
3260-1

etc


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.