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


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

    Hope this will help

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

  • Once you have tested the macro, feel free to comment

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!