Forum Discussion
Solved
Getting concat function to insert a specified number of digits
I am working on a Document Registry and each Document will be assigned a 3 digit number starting at 001. I have formatted the cell so it shows as a 3 digit number but when I use the concat function to bring the whole identification number together, it shortens it to 1 digit (removing the 0's) but I need my document identification number to stay in the same format.
Ex.
This is what I need: 2022-01-QA-PUB-001-E
This is what I get: 2022-1-QA-PUB-1-E
I have tried using both the left and right functions =left(a1,2) or =right(a1, 3) and I still lose my 0's both ways. please help!
in H1:
=CONCAT(TEXT(DATE(A1,B1,1),"yyyy-mm"), "-", C1, "-", D1, "-", TEXT(E1,"000"), "-", F1)if you run a recent version of Excel, in J1:
=TEXTJOIN("-",,TEXT(DATE(A1,B1,1),"yyyy-mm"), C1:D1, TEXT(E1,"000"), F1)
in H1:
=CONCAT(TEXT(DATE(A1,B1,1),"yyyy-mm"), "-", C1, "-", D1, "-", TEXT(E1,"000"), "-", F1)if you run a recent version of Excel, in J1:
=TEXTJOIN("-",,TEXT(DATE(A1,B1,1),"yyyy-mm"), C1:D1, TEXT(E1,"000"), F1)- This is great!!! And it worked!!! Thank you so much, haha, there are not enough excel experts and platforms out there for support and this is so much appreciated!
- Glad someone could help + Thanks for providing feedack
For next time(s): don't forget Excel version + Windows/MacOS
Is that exact format, i.e in 2022-1-QA-PUB-1-E numbers are always on second and fifth positions, or that could be different?
- The format can be changed for sure, we are in development mode. The first 1 refers to the first month of the year which is preferred in a 2 digit numerical format. The second 1 indicated the sequential number and preferably it would be in a 3 digit numerical format. The dashes aren't necessary at all but having them there or removing them doesn't seem to change my ability to get those digits in there. Hope that all makes sense.
