Forum Discussion

Clara2475's avatar
Clara2475
Copper Contributor
Mar 15, 2023

Custom cell format

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

Excel
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Mar 15, 2023

    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

Share

Resources