Combine 6 columns into 1 define the spacing

[Barneye]

I have an Excel workbook with 6 columns. I need to combine them into one cell but maintain a specific number of characters per column.
Example:

Name Orders Dollars MTD Ord MTD Doll % of goal
Name 1 $1,000 10 $1,000,000 15%

Name would need to be 8 characters no matter the contents. Dollars would need to be 8 digits etc......

It needs to automatic, so a formula would be easiest.
This is sent to a LED sign and needs very specific formating.

Any suggestions?

 

[SkipVought]

Hi,

I put the character width in row 1, headings in row 2, data/formula in row 3

[tt]
=LEFT(A3&REPT(" ",MAX(A$1-LEN(A3),A$1)),A$1)
[/tt]
drag across to get each column

finally concatenate to make one string.

Skip,
[sub]
[red][/red]
[/sub]

 

[Barneye]

Works Great!

Only problem is how to I format the cells? After using the formula, I cannot format the Dollar columns as currency and the % column as percent.

 

[SkipVought]

You must use the TEXT function
[tt]
TEXT(cellRef, Format)
[/tt]
where format is like "$#,###" for instance.

Skip,
[sub]
[red][/red]
[/sub]