Custom Formatting - Number & Text

SeaTigr

New Member
Joined
Sep 16, 2008
Messages
7
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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>
 
Last edited:
Upvote 0
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

<tbody>
</tbody>










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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
These could be an approach



Custom Format:
"TO01-2019-"000

Enters "3", Result:
086cb2e4df18454f96ab627ea7c82f7a.jpg


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

Enters "1.003", Result:
072fc8048bd3b12bb5ed0296d97dd397.jpg
 
Upvote 0
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:
Upvote 0
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
[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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top