Adding zeroes

E

ekhawaja

Board Regular
Joined
Dec 16, 2018
Messages
60
Office Version
  1. 365
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-130710ZE-01307
05WR-1405WR-00014
05PL-105PL-00001
33UA85933UA00859
05PW85905PW00859
05PA-100205PA-01002
05WC-TT199-105WC-TT00199-01
05WC-TT20-0105WC-TT00020-01
05WC-PV1490-105WC-PV01490-01
05PL-1A05PL-00001A
05UA859A05UA00859A
33WC-AXL120J-133WC-AXL01320J-01



 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
How do you determine the length for the values with no dash? Is it always after 4 characters?
 
Upvote 0
dreid1011 said:
How do you determine the length for the values with no dash? Is it always after 4 characters?
Click to expand...
the ones with no dash, so 05PW859, the pattern is after the first set of ALPHABETS, add zeros until the length of numbers is five. No it is not always after the 4 characters, but it is always after the dash or after the alphabets (no dash case) , or after the dash alphabets
 
Upvote 0
ekhawaja said:
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-130710ZE-01307
05WR-1405WR-00014
05PL-105PL-00001
33UA85933UA00859
05PW85905PW00859
05PA-100205PA-01002
05WC-TT199-105WC-TT00199-01
05WC-TT20-0105WC-TT00020-01
05WC-PV1490-105WC-PV01490-01
05PL-1A05PL-00001A
05UA859A05UA00859A
33WC-AXL120J-133WC-AXL01320J-01



Click to expand...
Basically, rule is:
a) if Number string is detected after the dash - add leading 0s to the number string so the size of NUMBER string is five (there can be a letter at the end (row 10 example). See row 1,2,3,6,10
b) no dash and number string is detected after the alphabets- add leading 0s to the number string so the size of NUMBER string is five. See row 4,5,11
c) number string is detected after dash & alphabets - add leading 0s to the number string so the size of NUMBER string is five. See row 7,8,9,12


I have 5000 row data that I need to apply this too, the above is the pattern noticed. If only a) and b) is possible, I'm happy with that, I can do c) manually (looks tricky to formulate).
 
Upvote 0
I understand what you are trying to achieve, but unfortunately, I think this one is beyond me. I just can't piece together how to find a variable length of numbers in the middle of the string, then add leading zeroes to it. It would be one thing if the strings didn't vary in length. What I have been able to come up with so far only satisfies the first dash or the second, and that if there are no letters preceding the numbers after the dash. I am hoping someone more knowledgeable can assist.
 
Upvote 0
Maybe this.

Book1.xlsx
ABCD
1TextFormulaDesiredCheck
210ZE-130710ZE-0130710ZE-01307TRUE
305WR-1405WR-0001405WR-00014TRUE
405PL-105PL-0000105PL-00001TRUE
533UA85933UA0085933UA00859TRUE
605PW85905PW0085905PW00859TRUE
705PA-100205PA-0100205PA-01002TRUE
805WC-TT199-105WC-TT00199-0105WC-TT00199-01TRUE
905WC-TT20-0105WC-TT00020-0105WC-TT00020-01TRUE
1005WC-PV1490-105WC-PV01490-0105WC-PV01490-01TRUE
1105PL-1A05PL-00001A05PL-00001ATRUE
1205UA859A05UA00859A05UA00859ATRUE
1333WC-AXL120J-133WC-AXL00120J-0133WC-AXL00120J-01TRUE
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=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:D13D2=B2=C2
Lambda Functions
NameFormula
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))))
 
Upvote 0
Cubist said:
Maybe this.

Book1.xlsx
ABCD
1TextFormulaDesiredCheck
210ZE-130710ZE-0130710ZE-01307TRUE
305WR-1405WR-0001405WR-00014TRUE
405PL-105PL-0000105PL-00001TRUE
533UA85933UA0085933UA00859TRUE
605PW85905PW0085905PW00859TRUE
705PA-100205PA-0100205PA-01002TRUE
805WC-TT199-105WC-TT00199-0105WC-TT00199-01TRUE
905WC-TT20-0105WC-TT00020-0105WC-TT00020-01TRUE
1005WC-PV1490-105WC-PV01490-0105WC-PV01490-01TRUE
1105PL-1A05PL-00001A05PL-00001ATRUE
1205UA859A05UA00859A05UA00859ATRUE
1333WC-AXL120J-133WC-AXL00120J-0133WC-AXL00120J-01TRUE
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=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:D13D2=B2=C2
Lambda Functions
NameFormula
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))))
Click to expand...
oh wow, let me try this, i never used Lambda function before, this is interesting....THANK YOU SO MUCH, apprciate your help, will try and let you know!
 
Upvote 0
Cubist said:
Maybe this.

Book1.xlsx
ABCD
1TextFormulaDesiredCheck
210ZE-130710ZE-0130710ZE-01307TRUE
305WR-1405WR-0001405WR-00014TRUE
405PL-105PL-0000105PL-00001TRUE
533UA85933UA0085933UA00859TRUE
605PW85905PW0085905PW00859TRUE
705PA-100205PA-0100205PA-01002TRUE
805WC-TT199-105WC-TT00199-0105WC-TT00199-01TRUE
905WC-TT20-0105WC-TT00020-0105WC-TT00020-01TRUE
1005WC-PV1490-105WC-PV01490-0105WC-PV01490-01TRUE
1105PL-1A05PL-00001A05PL-00001ATRUE
1205UA859A05UA00859A05UA00859ATRUE
1333WC-AXL120J-133WC-AXL00120J-0133WC-AXL00120J-01TRUE
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=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:D13D2=B2=C2
Lambda Functions
NameFormula
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))))
Click to expand...
is it under the Name Manager that I input the Lambda function? Sorry I have never used this before. Thank you
 
Upvote 0
With a UDF

VBA Code: 
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
ABCD
110ZE-130710ZE-0130710ZE-01307
205WR-1405WR-0001405WR-00014
305PL-105PL-0000105PL-00001
433UA85933UA0085933UA00859
505PW85905PW0085905PW00859
605PA-100205PA-0100205PA-01002
705WC-TT199-105WC-TT00199-0105WC-TT00199-01
805WC-TT20-0105WC-TT00020-0105WC-TT00020-01
905WC-PV1490-105WC-PV01490-0105WC-PV01490-01
1005PL-1A05PL-00001A05PL-00001A
1105UA859A05UA00859A05UA00859A
1233WC-AXL120J-133WC-AXL00120J-0133WC-AXL00120J-01
Sheet1
Cell Formulas
RangeFormula
D1:D12D1=jec(A1)
 
Upvote 0
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,215,124
Messages
6,123,189
Members
449,090
Latest member
bes000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top