Adding Spaces to the end of a value

[JohnGuts]

I am trying to add blank spaces at the end of a value so that each cel
is the same length.

In column A, I have names:

A1
Jones
Smith
Richardson
Blacksmith

In column B, each cell is 19 char in length. I want each cell in B to
the same cell in A (i.e B1=A1). However, I need them to all be the sam
length. To do so, I think I have to add trailing spaces to the names s
they all are 19 char in length.

Is is possible to add trailing spaces automatically to make all th
values the same length (i.e. LEN(B1)=LEN(B2)=LEN(B3) etc)?

Thanks for any help

 

[Dave Peterson]

If you know the length is always 19:

=left(a1&rept(" ",19),19)

If you don't know the length, you can use something like:

=LEFT(A1&REPT(" ",MAX(LEN($A$1:$A$100))),MAX(LEN($A$1:$A$100)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[JohnGuts]

Thanks for the tip. All the suggestions worked. I still have my
original problem. I am trying to copy my data from Excel into MS
Outlook in plain text mode, and I just can't get the columns to
line-up. I thought that if I made the columns the same size by
appending spaces at the end, it would help.

It did make column A and B lineup right, but it has a large space
between the 2. It appears that when I copy from Excel and paste into
Outlook, it inserts TABs instead of spaces.

Anyone have any ideas? Thanks!

 

[JohnGuts]

I am trying to draw data from various sheets to compile a mail message.
I am limited by my mail server in how far across the message I can go
before the server wraps the text around to the next line. When it
does, my column headers appear on 2 rows making it unreadable.

I have 9 columns that are:

Name Office 8/01 8/02 8/03 8/04 8/05 8/06 8/07

Under the date column headers will be hours to be worked. All the data
is generated from other sheets, I just need to take the data, put it
into Outlook, and have the columns all line up.

I hope I explained ti well enough for you to understand. Please help!

 

[Dave Peterson]

Maybe use an additional cell to build the string you want--include as many cells
as you need in that formula:

=left(a1&rept(" ",19),19) & text(b1,"###,###.00") & " " & ...