Custom Formatting - Number & Text

[SeaTigr]

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.

 

[DanteAmor]

try this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:139.72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >TO01-2019-001</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >="TO" & TEXT(A1,"00") & "-" & TEXT(TODAY(), "yyyy") & "-" & TEXT(A1, "000")</td></tr></table></td></tr></table>

 

[SeaTigr]

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.

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Task Order
[/TD]
[TD]01
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Action Item #
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]TO01-2019-001
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]TO01-2019-002
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










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.

 

[Joe4]

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.

 

[SeaTigr]

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.

 

[Joe4]

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

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

 

[DanteAmor]

These could be an approach



Custom Format:
"TO01-2019-"000

Enters "3", Result:

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

Enters "1.003", Result:

 

[SeaTigr]

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?

 

[Joe4]

A minor update to handle deleting entries:

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
[COLOR=#ff0000]'       Handle deleted entries
        If Len(Target) = 0 Then Exit Sub[/COLOR]
'       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

 

[SeaTigr]

That worked. You are amazing. Thank you!