Adding zeroes

[ekhawaja]

Hello,

Looking for a formula that needs to ensure that after the first "-" or if there is no dash then after the letter commodity code, the number length should be 5 digits by adding leading zeroes. If there are two "-", second dash length should be changed to two by adding a single leading 0.

See below:

Thanks in advance!

10ZE-1307	10ZE-01307
05WR-14	05WR-00014
05PL-1	05PL-00001
33UA859	33UA00859
05PW859	05PW00859
05PA-1002	05PA-01002
05WC-TT199-1	05WC-TT00199-01
05WC-TT20-01	05WC-TT00020-01
05WC-PV1490-1	05WC-PV01490-01
05PL-1A	05PL-00001A
05UA859A	05UA00859A
33WC-AXL120J-1	33WC-AXL01320J-01

 

[ekhawaja]

Maybe this.

Book1.xlsx
	A	B	C	D
1	Text	Formula	Desired	Check
2	10ZE-1307	10ZE-01307	10ZE-01307	TRUE
3	05WR-14	05WR-00014	05WR-00014	TRUE
4	05PL-1	05PL-00001	05PL-00001	TRUE
5	33UA859	33UA00859	33UA00859	TRUE
6	05PW859	05PW00859	05PW00859	TRUE
7	05PA-1002	05PA-01002	05PA-01002	TRUE
8	05WC-TT199-1	05WC-TT00199-01	05WC-TT00199-01	TRUE
9	05WC-TT20-01	05WC-TT00020-01	05WC-TT00020-01	TRUE
10	05WC-PV1490-1	05WC-PV01490-01	05WC-PV01490-01	TRUE
11	05PL-1A	05PL-00001A	05PL-00001A	TRUE
12	05UA859A	05UA00859A	05UA00859A	TRUE
13	33WC-AXL120J-1	33WC-AXL00120J-01	33WC-AXL00120J-01	TRUE
Sheet2

Cell Formulas
Range	Formula
B2:B13	B2	=LET(a,TEXTBEFORE(A2,"-",,,,A2),b,IFERROR(CHOOSE(SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-"),TEXTAFTER(A2,"-",1),TEXTBEFORE(TEXTAFTER(A2,"-"),"-")),""),c,TEXTAFTER(A2,"-",2,,,""),TEXTJOIN("-",TRUE,updateStr(a),TEXT(updateStr(b),"00000"),TEXT(c,"00")))
D2:D13	D2	=B2=C2

Lambda Functions
Name	Formula
updateStr	=LAMBDA(str,LET(firstChar,IFERROR(FIND(UNICHAR(SEQUENCE(1,26,65)),str),0), end,MAX(IFERROR(LET(alpha,UNICHAR(SEQUENCE(1,26,65)),FIND("@",SUBSTITUTE(str,alpha,"@",(LEN(str)-LEN(SUBSTITUTE(str,alpha,"")))/LEN(alpha)))),0)), start,IF(end=LEN(str),LARGE(UNIQUE(HSTACK(end,firstChar),1),2),end), noLastChar,LEFT(str,start)&TEXT(MID(str,start+1,10),"00000"), hasLastChar,LEFT(str,start)&TEXT(MID(str,start+1,LEN(str)-start-1),"00000")&RIGHT(str,1), IF(NOT(ISNUMBER(NUMBERVALUE(MID(str,LEN(str)-1,1)))),str,IF(end=start,noLastChar,hasLastChar))))

@Cubist thank you so much again!. The only time i am getting different result is when i have 3 letter string at the beginning:

05V-330 is turning out as 00005V-00330 (too many 0s at the front)

 

[ekhawaja]

With a UDF

Function jec(c As String) As String
 Dim ar
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "[A-Z-][0-9]+"
   Set ar = .Execute(c)
   If ar.Count = 1 Then
     jec = Replace(c, ar(0), Left(ar(0), 1) & Format(Mid(ar(0), 2), "00000"))
   Else
     jec = Replace(Replace(c, ar(1), Left(ar(1), 1) & Format(Mid(ar(1), 2), "00")), ar(0), Left(ar(0), 1) & Format(Mid(ar(0), 2), "00000"))
   End If
 End With
End Function

Book12.xlsm
	A	B	C	D
1	10ZE-1307	10ZE-01307		10ZE-01307
2	05WR-14	05WR-00014		05WR-00014
3	05PL-1	05PL-00001		05PL-00001
4	33UA859	33UA00859		33UA00859
5	05PW859	05PW00859		05PW00859
6	05PA-1002	05PA-01002		05PA-01002
7	05WC-TT199-1	05WC-TT00199-01		05WC-TT00199-01
8	05WC-TT20-01	05WC-TT00020-01		05WC-TT00020-01
9	05WC-PV1490-1	05WC-PV01490-01		05WC-PV01490-01
10	05PL-1A	05PL-00001A		05PL-00001A
11	05UA859A	05UA00859A		05UA00859A
12	33WC-AXL120J-1	33WC-AXL00120J-01		33WC-AXL00120J-01
Sheet1

Cell Formulas
Range	Formula
D1:D12	D1	=jec(A1)

@JEC , this macro seems to be working with all criteria, thank you so much, the community here is complete genius!! =) @JEC @Cubist @dreid1011 . Made my work so much easier!

 

[dreid1011]

I am glad you got a solution after I tried and realized it was well beyond my logic bending abilities. (After looking at the solutions, my head it still swirling).

 

[JEC]

Glad it worked, cheers!

 

[Cubist]

@Cubist thank you so much again!. The only time i am getting different result is when i have 3 letter string at the beginning:

05V-330 is turning out as 00005V-00330 (too many 0s at the front)

With a small adjustment, this should fix that. I prefer @JEC's solution over this, btw.

Book1
	A	B	C	D
1	Text	Formula	Desired	Check
2	10ZE-1307	10ZE-01307	10ZE-01307	TRUE
3	05WR-14	05WR-00014	05WR-00014	TRUE
4	05PL-1	05PL-00001	05PL-00001	TRUE
5	33UA859	33UA00859	33UA00859	TRUE
6	05PW859	05PW00859	05PW00859	TRUE
7	05PA-1002	05PA-01002	05PA-01002	TRUE
8	05WC-TT199-1	05WC-TT00199-01	05WC-TT00199-01	TRUE
9	05WC-TT20-01	05WC-TT00020-01	05WC-TT00020-01	TRUE
10	05WC-PV1490-1	05WC-PV01490-01	05WC-PV01490-01	TRUE
11	05PL-1A	05PL-00001A	05PL-00001A	TRUE
12	05UA859A	05UA00859A	05UA00859A	TRUE
13	33WC-AXL120J-1	33WC-AXL00120J-01	33WC-AXL00120J-01	TRUE
14	05V-330	05V-00330
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=B2=C2
B2:B14	B2	=LET(h,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-"),a,TEXTBEFORE(A2,"-",,,,A2),b,IFERROR(CHOOSE(h,TEXTAFTER(A2,"-",1),TEXTBEFORE(TEXTAFTER(A2,"-"),"-")),""),c,TEXTAFTER(A2,"-",2,,,""),TEXTJOIN("-",TRUE,IF(h>0,a,updateStr(a)),TEXT(updateStr(b),"00000"),TEXT(c,"00")))

Lambda Functions
Name	Formula
updateStr	=LAMBDA(str,LET(firstChar,IFERROR(FIND(UNICHAR(SEQUENCE(1,26,65)),str),0), end,MAX(IFERROR(LET(alpha,UNICHAR(SEQUENCE(1,26,65)),FIND("@",SUBSTITUTE(str,alpha,"@",(LEN(str)-LEN(SUBSTITUTE(str,alpha,"")))/LEN(alpha)))),0)), start,IF(end=LEN(str),LARGE(UNIQUE(HSTACK(end,firstChar),1),2),end), noLastChar,LEFT(str,start)&TEXT(MID(str,start+1,10),"00000"), hasLastChar,LEFT(str,start)&TEXT(MID(str,start+1,LEN(str)-start-1),"00000")&RIGHT(str,1), IF(NOT(ISNUMBER(NUMBERVALUE(MID(str,LEN(str)-1,1)))),str,IF(end=start,noLastChar,hasLastChar))))

 

[ekhawaja]

With a small adjustment, this should fix that. I prefer @JEC's solution over this, btw.

Book1
	A	B	C	D
1	Text	Formula	Desired	Check
2	10ZE-1307	10ZE-01307	10ZE-01307	TRUE
3	05WR-14	05WR-00014	05WR-00014	TRUE
4	05PL-1	05PL-00001	05PL-00001	TRUE
5	33UA859	33UA00859	33UA00859	TRUE
6	05PW859	05PW00859	05PW00859	TRUE
7	05PA-1002	05PA-01002	05PA-01002	TRUE
8	05WC-TT199-1	05WC-TT00199-01	05WC-TT00199-01	TRUE
9	05WC-TT20-01	05WC-TT00020-01	05WC-TT00020-01	TRUE
10	05WC-PV1490-1	05WC-PV01490-01	05WC-PV01490-01	TRUE
11	05PL-1A	05PL-00001A	05PL-00001A	TRUE
12	05UA859A	05UA00859A	05UA00859A	TRUE
13	33WC-AXL120J-1	33WC-AXL00120J-01	33WC-AXL00120J-01	TRUE
14	05V-330	05V-00330
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=B2=C2
B2:B14	B2	=LET(h,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-"),a,TEXTBEFORE(A2,"-",,,,A2),b,IFERROR(CHOOSE(h,TEXTAFTER(A2,"-",1),TEXTBEFORE(TEXTAFTER(A2,"-"),"-")),""),c,TEXTAFTER(A2,"-",2,,,""),TEXTJOIN("-",TRUE,IF(h>0,a,updateStr(a)),TEXT(updateStr(b),"00000"),TEXT(c,"00")))

Lambda Functions
Name	Formula
updateStr	=LAMBDA(str,LET(firstChar,IFERROR(FIND(UNICHAR(SEQUENCE(1,26,65)),str),0), end,MAX(IFERROR(LET(alpha,UNICHAR(SEQUENCE(1,26,65)),FIND("@",SUBSTITUTE(str,alpha,"@",(LEN(str)-LEN(SUBSTITUTE(str,alpha,"")))/LEN(alpha)))),0)), start,IF(end=LEN(str),LARGE(UNIQUE(HSTACK(end,firstChar),1),2),end), noLastChar,LEFT(str,start)&TEXT(MID(str,start+1,10),"00000"), hasLastChar,LEFT(str,start)&TEXT(MID(str,start+1,LEN(str)-start-1),"00000")&RIGHT(str,1), IF(NOT(ISNUMBER(NUMBERVALUE(MID(str,LEN(str)-1,1)))),str,IF(end=start,noLastChar,hasLastChar))))

this seems to be working as well!!

 

[Peter_SSs]

@JEC , this macro seems to be working with all criteria, thank you so much, the community here is complete genius!! =) @JEC @Cubist @dreid1011 . Made my work so much easier!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.