Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Custom Formatting - Number & Text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Custom Formatting - Number & Text

    Hello, everyone!

    I am trying to create a spreadsheet to track action items for contract task orders. To make things easier for me to avoid formatting mistakes (the spreadsheet will be editable by more than just me) I would like to create a custom formatting that takes an entered ID number - e.g., 1 - and turns it into a unique alphanumeric action item number in the following format: TO##-YYYY-ID#. E.g., TO01-2019-001. The task order number will be specified in another cell on the worksheet.

    What I tried in the custom format field was: "TO"TEXT(A1)"-"yyyy"-"000
    Excel didn't take the formatting.

    Next I tried "TO01-"yyyy"-"000
    Excel didn't take the formatting.

    Next I tried "TO01-"yyyy"-"General
    Excel took the formatting. However, when I used 15 as the ID number to test a number greater than 10 I received the following string: TO01-1900-0.5
    Clearly, there are two issues with that - it's not 1900, and 0.5 =\= 15.

    Please help.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Custom Formatting - Number & Text

    try this:

     AB
    11TO01-2019-001

    CellFormula
    B1="TO" & TEXT(A1,"00") & "-" & TEXT(TODAY(), "yyyy") & "-" & TEXT(A1, "000")
    Last edited by DanteAmor; May 21st, 2019 at 11:34 PM.
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Formatting - Number & Text

    Thanks. That helps with formatting specific cells, but when I right-click on the whole column, select Format Cells, then choose Custom and enter it, Excel doesn't like it no matter how I try to modify it.

    What I'm trying to do is set up the column so that a user enters a number and Excel auto-formats it.

    A B
    1 Task Order 01
    2 Action Item #
    3 TO01-2019-001
    4 TO01-2019-002
    5










    Using the above table as a template, I'd like to set up the custom formatting so that if a user enters "3" into cell A5 and presses Enter, Excel will auto-format it and display TO01-2019-003.

    Since I don't know how many action items will be generated for a given task order, and since multiple people will have access to the spreadsheet, I'd like to create a column that will auto-format numbers whenever a user enters a new action item, as opposed to me having to go in and apply the formatting cell by cell.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Custom Formatting - Number & Text

    Custom Formatting may not be your answer here (unless you only use it for new record creation, and never show any past history).

    The issue is that like formulas, they are dynamic, and the value can change, especially if you try to incorporate a year portion. The year portion won't be "frozen in time", it will automatically change as the year changes. So all your records will always show the current year.

    I think your best bet is some automated VBA code. One that either converts an entry, or one that creates the code for you and populates it.
    Last edited by Joe4; May 22nd, 2019 at 09:36 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Formatting - Number & Text

    Ahhhh...I didn't know that. Yes, I need the AI# to be frozen in time - i.e., TO01-2019-001 will always and forevermore be TO01-2019-001 after it is created, even if the current year is no longer 2019.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Custom Formatting - Number & Text

    OK, assuming that B1 holds the value that we use for the "T" portion of the number (be sure to enter as a number, not text), and if they enter a number in column A under row 1, then the following code should automatically update that. This code runs automatically, so it needs to be put in the correct place. Here is how to do that:
    - Right-click on the sheet tab name at the bottom of the screen
    - Select "View Code"
    - Paste this code in the resulting VB Editor window
    - Do NOT change the name of the code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim new_id As String
    
    '   Only run when a single cell in column A under row 1 is entered
        If Target.CountLarge > 1 Or Target.Row = 1 Or Target.Column > 1 Then Exit Sub
    
    '   Only run when a number is entered in column A
        If IsNumeric(Target) Then
    '       Update value
            Application.EnableEvents = False
            new_id = "T" & Format(Range("B1"), "000") & "-" & Format(Date, "yyyy") & "-" & Format(Target, "000")
            Target = new_id
            Application.EnableEvents = True
        End If
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,192
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Custom Formatting - Number & Text

    These could be an approach



    Custom Format:
    "TO01-2019-"000

    Enters "3", Result:


    Custom Format:
    "TO"00"-2019".000

    Enters "1.003", Result:
    Regards Dante Amor

  8. #8
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Formatting - Number & Text

    That works really well, thank you for the script!

    I suppose this is my last question: if I try to delete an entry (i.e., simulating a user who made a mistake and added an entry that should not be an action item) the three digits at the end disappear, but the "TO01-2019-" does not. I tried clicking on a cell and pressing Delete, I tried clicking on a cell and deleting the entry in the Fx function bar, I tried clicking on a cell and right-clicking to select "Clear Contents", all with no success. Is my only option to delete the entire row?
    Last edited by SeaTigr; May 22nd, 2019 at 12:45 PM.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Custom Formatting - Number & Text

    A minor update to handle deleting entries:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim new_id As String
    
    '   Only run when a single cell in column A under row 1 is entered
        If Target.CountLarge > 1 Or Target.Row = 1 Or Target.Column > 1 Then Exit Sub
    
    '   Only run when a number is entered in column A
        If IsNumeric(Target) Then
    '       Handle deleted entries
            If Len(Target) = 0 Then Exit Sub
    '       Update value
            Application.EnableEvents = False
            new_id = "T" & Format(Range("B1"), "000") & "-" & Format(Date, "yyyy") & "-" & Format(Target, "000")
            Target = new_id
            Application.EnableEvents = True
        End If
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Formatting - Number & Text

    That worked. You are amazing. Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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