• Custom Number Formats (Excel 2000)

    Author
    Topic
    #369697

    Hi guys!
    I have a tag number gabcdef122a. I want to create a custom number format and have it format that cell to g-abcdef1-22-a. Any hint on how I can have excel give me a single text placeholder? I tried using @, but I can quite get it to work.

    Much Thanks in Advance!

    Viewing 1 reply thread
    Author
    Replies
    • #582753

      Try,

      “g-abcdef1-“#”-a”

      Andrew C

    • #582755

      Looking at it again, if you want to get that display be entering 122, use

      “g-abcdef”0-00-“a”

      Andrew C

      • #582784

        Thanks, Andrew.

        But I didn’t make myself clear. I need to enter a format that is more generalized. For instance,

        Text Character-6 Text Characters/1 number character-2number characters-Text Characters

        I used the g-abcdef1-22-a as an example. The next tag could read h-afgtyu3-45-c. It’s not the actual characters, but rather a general format with the placeholders that I need since the tag function like identifiers and will change each time.

        Any other suggestions?

        • #582801

          I don’t believe that you can do individual characters in a custom number format. You will have to use the worksheet change event instead:

          Copy the code below, paste it into Word, select all, and copy the Word text (this gets rid of HTML formatting).
          Right-click on the Excel sheet tab and select View Code.
          Paste the code into the code panel (upper-right).
          If the data will be entered into a column other than A:A, correct the range in line 6 of the code.
          Press to return to Excel.
          Save the workbook.
          Test it.

          Option Explicit
          Private Sub Worksheet_Change(ByVal Target As Range)
          Dim c As Range
          Dim oRangeToFormat As Range
          '   Change A:A below to the range that you want to auto-format
              Set oRangeToFormat = ActiveSheet.Range("A:A")
              If Not Intersect(Target, oRangeToFormat) Is Nothing Then
                  Application.EnableEvents = False
                  For Each c In Intersect(Target, ActiveSheet.Range("A:A"))
          '           Format 1234567890ABC... as 1-2345678-90-ABC...
          '           So     gabcdef123a becomes g-abcdef1-23-a
                      c.Value = Left(c.Value, 1) & "-" & _
                          Mid(c.Value, 2, 7) & "-" & _
                          Mid(c.Value, 9, 2) & "-" & _
                          Right(c.Value, Len(c.Value) - 10)
                  Next c
                  Application.EnableEvents = True
              End If
          End Sub

          Note: you may want to do some error checking, I just inserted dashes. HTH –Sam

        • #582983

          Steve,

          I believe the attached workbook will do what you want in Excel. Note that I use the Text function, which has a lot of the same (but not all) capabilities of custom formating.

          Also note that I followed the format in your 2nd posting, which had a slash/ between the 6 characters and the 1 digit number. Your original posting did not have that. If your first posting is actually what you want, hopefully you can delete the necessary parts of my solution. If not, post back.

          Also to make it easier to copy my formula (after you’ve fixed my worksheet to format exactly how you want it per above para), there a few ways to avoid typing this whole thing:
          1. copy cell H3 and do a Paste Special Formula to your worksheet
          2. in my worksheet in H3, delete the = sign which will give you a long string. Copy and paste H3 to your workbook. Put the equal sign back and adjust the cell references.

          HTH.

          Fred

    Viewing 1 reply thread
    Reply To: Custom Number Formats (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: