Try:

=Text(row(),"0000")


That didn't work for me, but this did

Put Row 1 0001 in Col B
Put 0001 in Col C
Put function =RIGHT(B2,4) in Col D
Put function =CONCATENATE(C2," ",D2) in Col E
Copy down 9999 rows

Use Copy & Paste Special/Values to put Col E info into an empty column and
delete Cols B thru E.

It's not very elegant, but it does the job quick and EZ.

good luck