Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Custom cell format

Copper Contributor

Hi,

 

I'm looking to create a format for my cells so they include text, numbers and place holders that displays insignificant zeros (quite similar to a postal code). Not all inputs will have 5 digits after the dash so I need them to be replaced with a '0' if not there.

It needs to be in below format:

 

BB22-01234

 

I've tried @@##"-"0000# but that doesn't seem to work. Any suggestions please?

 

TIA

1 Reply

@Clara2475 As far as I know, you cannot achieve this with a conditional format.

This VBA code in the code module belonging to the worksheet should do the trick:

 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
    If Target.Cells.Count = 1 Then
        If Target.Value Like "??##-#*" Then
            v = Split(Target.Value, "-")
            Application.EnableEvents = False
            Target.Value = v(0) & "-" & v(1) & String(4 - Len(v(1)), "0")
            Application.EnableEvents = True
        Else
            MsgBox "Invalid format, please enter two characters, two digits a dash and one to four digits:" & vbNewLine & _
            "AA11-0000"
        End If
    End If
End Sub