format of number with text and auto generate code

  • cell A1 typed in value is "06-Dec-2019", cell B1 is what user needs to type numbers only, once cell B1 value is typed in let's say "1", then cell B1 will appear as "AB19120601CD" instead of "1"

    AB19120601CD = concatenate("AB",right(year($A$1),2),RIGHT(MONTH($A$1),2),Right(Day($A$1),2),text($B$1,"00"),"CD")

    how can i make the above happen which I can't use format as it runs automatically by cell A1 and cell B1

    pls help ||

  • Hello,

    You could test following

    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Count > 1 Then Exit Sub
    3. If Target = "" Then Exit Sub
    4. Application.EnableEvents = False
    5. Target = "AB" & Right(Year(Range("A1")), 2) & Month(Range("A1")) & Format(Day(Range("A1")), "00") & Target & "CD"
    6. Application.EnableEvents = True
    7. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just the bottom right corner...:)