Need to count the current cell and fix if under 3 characters.

  • This is the current code......=MID(SUBSTITUTE(IF(MID(A6,1,1)="A","UA"&REPLACE(A6,1,1,""),"UE"&REPLACE(A6,1,2,"")),"-",""),1,4)......Now I need to add to this code and see if it is under 4 characters and if it is I would like it to add a "0" between the 2nd and 3rd character.
    Example UA1 to UA01 or UE1 to UE01. How can I make this happen?

  • [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 829"]

    [tr]


    [td]

    #

    [/td]


    [td]

    Date

    [/td]


    [td]

    look at the first character, A=UA, E=UE

    [/td]


    [td]

    next add the first 2 numbers and remove any "-"

    [/td]


    [td]

    check to see if the number of characters is under 4, and if so add a "0" between the 2nd and 3rd characters

    [/td]


    [td]

    now I need to remove the "/" out of the Generation date and combine

    [/td]


    [/tr]


    [tr]


    [td]

    A1

    [/td]


    [td]

    12/23/2019

    [/td]


    [td]

    UA

    [/td]


    [td]

    UA1

    [/td]


    [td]

    UA01

    [/td]


    [td]

    UA01122319

    [/td]


    [/tr]


    [tr]


    [td]

    A12

    [/td]


    [td]

    12/2/2019

    [/td]


    [td]

    UA

    [/td]


    [td]

    UA12

    [/td]


    [td]

    UA12

    [/td]


    [td]

    UA12122419

    [/td]


    [/tr]


    [tr]


    [td]

    A123

    [/td]


    [td]

    12/25/2019

    [/td]


    [td]

    UA

    [/td]


    [td]

    UA12

    [/td]


    [td]

    UA12

    [/td]


    [td]

    UA12122519

    [/td]


    [/tr]


    [tr]


    [td]

    ES1

    [/td]


    [td]

    12/26/2019

    [/td]


    [td]

    UE

    [/td]


    [td]

    UE1

    [/td]


    [td]

    UE01

    [/td]


    [td]

    UE01122619

    [/td]


    [/tr]


    [tr]


    [td]

    ES12

    [/td]


    [td]

    12/27/2019

    [/td]


    [td]

    UE

    [/td]


    [td]

    UE12

    [/td]


    [td]

    UE12

    [/td]


    [td]

    UE12122719

    [/td]


    [/tr]


    [tr]


    [td]

    ES-458

    [/td]


    [td]

    12/28/2019

    [/td]


    [td]

    UE

    [/td]


    [td]

    UE45

    [/td]


    [td]

    UE45

    [/td]


    [td]

    UE12122819

    [/td]


    [/tr]


    [/TABLE]

  • Trythis


    =IF(LEN(MID(SUBSTITUTE(IF(MID(A2,1,1)="A","UA"&REPLACE(A2,1,1,""),"UE"&REPLACE(A2,1,2,"")),"-",""),1,4))<4,LEFT(MID(SUBSTITUTE(IF(MID(A2,1,1)="A","UA"&REPLACE(A2,1,1,""),"UE"&REPLACE(A2,1,2,"")),"-",""),1,4),2)&"0"&RIGHT(MID(SUBSTITUTE(IF(MID(A2,1,1)="A","UA"&REPLACE(A2,1,1,""),"UE"&REPLACE(A2,1,2,"")),"-",""),1,4),1)&TEXT(B2,"mdyy"),MID(SUBSTITUTE(IF(MID(A2,1,1)="A","UA"&REPLACE(A2,1,1,""),"UE"&REPLACE(A2,1,2,"")),"-",""),1,4)&TEXT(B2,"mdyy"))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.