Fill blank cells with spaces

  • Thread starter Thread starter pnascimento
  • Start date Start date
P

pnascimento

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.
 
This formula will give you 8 spaces:

=REPT(" ",8)

i.e. repeat a space 8 times.

Hope this helps.

Pete
 
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.
 
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
 
(e-mail address removed) wrote...
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.
Click to expand...

Is this for the contents or what Excel displays? If the latter, you
could use Fill horizontal alignment and a monospace typeface.
 
If you want to have all cells in column A filled with spaces up to
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 ge
an error (#VALUE!). If you expect this then adjust the formula with a
if condition.

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

You could also try automating this with a macro
 
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
 
Back
Top