Adding Spaces to the end of a value
 

I am trying to add blank spaces at the end of a value so that each cell
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 same
length. To do so, I think I have to add trailing spaces to the names so
they all are 19 char in length.

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

Thanks for any help!


--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=566432

Adding Spaces to the end of a value
 

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 wrote:

I am trying to add blank spaces at the end of a value so that each cell
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 same
length. To do so, I think I have to add trailing spaces to the names so
they all are 19 char in length.

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

Thanks for any help!

--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=566432

--

Dave Peterson

Adding Spaces to the end of a value
 

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
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=566432

Adding Spaces to the end of a value
 

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 a

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!


--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=566432

Adding Spaces to the end of a value
 

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") & " " & ...



JohnGuts wrote:

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
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=566432

--

Dave Peterson