Using SSIS or BCP you won't achieve your requirement.

Either you code it with .NET, or you use a macro to do the formatting.

Another option is to create a report in SSRS and export that to Excel.