Custom cell format

[Clara2475]

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

[#2]

@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