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.