Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Number Formats (Excel 2000)

    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!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Custom Number Formats (Excel 2000)

    Try,

    <big>"g-abcdef1-"#"-a"
    </big>

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Custom Number Formats (Excel 2000)

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

    <big>"g-abcdef"0-00-"a"
    </big>
    Andrew C

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Number Formats (Excel 2000)

    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?

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Number Formats (Excel 2000)

    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 <Alt> <F11> to return to Excel.
    Save the workbook.
    Test it.

    <pre>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</pre>


    Note: you may want to do some error checking, I just inserted dashes. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Custom Number Formats (Excel 2000)

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •