Concatenate - combine numbers & text

[weety]

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?

[NBVC]

To format numbers and dates into text strings like that you need to use the TEXT function.


e.g.


="BR"&TEXT(A2,"000000")&"ENGB"&SUBSTITUTE(TEXT(B2, "mm-yy"),"-","_")&"1"


Where A1 contains a number and B2 contains a date.


You can replace any text in quotes with cell references.



If you want an underscore instead of dash to separate month and year, you have to use the SUBSTITUTE function to replace the dash.

[weety]

That's excellent. Works fine. Many thanks

[ArviLaanemets]

An example which also ensures that only an unique sequential document number may be entered.