I agree with @Darren_Murphy’s method. It’s a lot safer than having a formula create the number…especially using the order number example. Otherwise with an arrayformula and dynamic numbering, it’s too easy to delete one row and have all order numbers get renumbered and messed up. I think the best way is to have one column with just a number and another column with the alpha characters, then build the complete order number with a template column.
I think to get the left padded zeros, you could take the number, divide by 1000, create a template to lock in the value and replace the decimal with nothing.