Formatting a String of Cells with proper Spacing

[dversloot1]

Hello,

I'm attempting to format a string of data in excel cleanly so that it looks presentable in an email.

I have a variety cells with different character lengths. I'm trying to string together Fields 1 through 4 in the desired format below; proper spacing between each field.

Desired Format Field 1 Field 2 Field 3 Field 4 DMR 65 58% ABC DMR 65 58% ABC VA 58 4% DEF VA 58 4% DEF <tbody> </tbody>

<tbody>
</tbody>

How can this be done?

Not sure if the table will show what I am trying to achieve. I would like field 2 to start 8 characters in from the beginning of the string, the 3rd field at 16 characters, and the 4th field at 24 characters in...

thanks,

Dan

 

[shg]

I use an add-in for posting to this board and others that pads cells contents to constant width and copies them to the clipboard. You can grab it at Add-In for posting and see if it's close enough to what you want. For your example,

      ------A------- ---B--- ---C--- ---D--- ---E---
  1   Desired Format Field 1 Field 2 Field 3 Field 4
  2   DMR 65 58% ABC DMR          65     58% ABC    
  3   VA 58 4% DEF   VA           58      4% DEF

 

[taurean]

How is % column formatted. Blind guess would be:
=C2&" "&D2&" "&TEXT(E2,"#%")&" "&F2

 

[dversloot1]

I can get that far, but I cannot get the fields to line up properly.
I hope to get to this output with a string:
DMR___65____ 58%_____ABC
VA____58_____4% _____ DEF
*without the underscores

 

[taurean]

Here's my idea based on assumption that %age is formatted data and not textual one.

	3	2	3	3
DMR 65 58% ABC	DMR	65	58%	ABC
VA 58 4% DEF	VA	58	4%	DEF

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Formula in Cell B1 [array formula (CTRL + SHIFT + ENTER) copied across]:
=MAX(LEN(B2:B3))
except D1:
=MAX(LEN(TEXT(D2:D3,"#%")))

Formula in A2:
=REPLACE(REPT(" ",$B$1+1),1,LEN(B2),B2)&REPLACE(REPT(" ",$C$1+1),1,LEN(C2),C2)&REPLACE(REPT(" ",$D$1+1),1,LEN(TEXT(D2,"#%")),TEXT(D2,"#%"))&REPLACE(REPT(" ",$E$1+1),1,LEN(E2),E2)

copy down till last row.

 

[dversloot1]

Worked great!

Thanks for understanding what I was getting at.

 

[taurean]

Fantastic, thanks for the feedback

 

[Barklie]

How did you get your code to come up within a scroll box on this page? It's much easier to read.

 

[shg]

Code tags.