SOLVED

# Getting concat function to insert a specified number of digits

Copper Contributor

# 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!

5 Replies

# Re: Getting concat function to insert a specified number of digits

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?

# Re: Getting concat function to insert a specified number of digits

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.
best response confirmed by denisepoirier (Copper Contributor)
Solution

# Re: Getting concat function to insert a specified number of digits

in H1:

``=CONCAT(TEXT(DATE(A1,B1,1),"yyyy-mm"), "-", C1, "-", D1, "-", TEXT(E1,"000"), "-", F1)``

``=TEXTJOIN("-",,TEXT(DATE(A1,B1,1),"yyyy-mm"), C1:D1, TEXT(E1,"000"), F1)``

# Re: Getting concat function to insert a specified number of digits

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!

# Re: Getting concat function to insert a specified number of digits

Glad someone could help + Thanks for providing feedack
For next time(s): don't forget Excel version + Windows/MacOS
1 best response

Accepted Solutions
best response confirmed by denisepoirier (Copper Contributor)
Solution

# Re: Getting concat function to insert a specified number of digits

in H1:

``=CONCAT(TEXT(DATE(A1,B1,1),"yyyy-mm"), "-", C1, "-", D1, "-", TEXT(E1,"000"), "-", F1)``

``=TEXTJOIN("-",,TEXT(DATE(A1,B1,1),"yyyy-mm"), C1:D1, TEXT(E1,"000"), F1)``