Concatenate - combine numbers & text
I'm using a spreadsheet to create unique document numbers. The document numbers are built up from various elements as follows.
Document type - a 2 character text entry e.g. BR
Document number - a unique and sequential number formatted to return as 6 digits e.g. 000001
Document language - a 4 char text entry e.g. ENGB
A date formatted as mm-yy
a revision number
When I use concatenate to bring all these together the formula converts the unique number to 1 and not 000001 and converts the date to a number. So what I get in this case is BR1ENGB-1_43800 instead of BR000001ENGB-1_12-19
I could enter the number and date fields as text which would return the right number but this adds a layer of complication for those using the spreadsheet. Any ideas?
|