Fill blank cells with spaces
 

Hi,
I was wondering if there's somehow I can fill n char width cells with
spaces.
i.e. If I have a blank cell with width "8", it will be filled with 8
spaces. It'd be something like format cells with "00000000", but with
spaces instead of zeros.

Thanks in advance.

Fill blank cells with spaces
 

This formula will give you 8 spaces:

=REPT(" ",8)

i.e. repeat a space 8 times.

Hope this helps.

Pete

Fill blank cells with spaces
 

The number of spaces would have to be acquired from what's left on a
cell.
If cell width is 15 and I have written "text", it'd replace it with
"text ".
There'd be a random number of cell widths.

Fill blank cells with spaces
 

I did reply to this earlier, but it seems to have got lost - if it
doesn't turn up by the morning I will repost my answer.

Pete

Fill blank cells with spaces
 

Fill blank cells with spaces
 

If you want to have all cells in column A filled with spaces up to 8
characters then try entering this formula in cell B1, and copy down.

=A1&REPT(" ",8-LEN(A1))

Note: If any cells in column A have more than 8 characters you will get
an error (#VALUE!). If you expect this then adjust the formula with an
if condition.

You can convert column B to values by copying these cells and then
edit-paste-special them as values. Only then would you be able to see
these extra spaces when you edit the cell. Presumably with a fixed
width font this will give the effect you are after.

You could also try automating this with a macro.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=524778

Fill blank cells with spaces
 

My earlier reply has still not turned up, so I'll try again. Your
second post mentions 15 characters, so here's a way of ensuring that
the text in cell A1 is exactly 15 characters wide:

=IF(LEN(A1)=15,LEFT(A1,15),A1&REPT(" ",15-LEN(A1)))

This truncates A1 if it is more than 15 characters to begin with,
otherwise it adds enough spaces at the end to make it up to 15
characters. The formula can be copied across and down to act on other
cells, as appropriate.

Hope this helps.

Pete