Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill blank cells with cells above data | Excel Worksheet Functions | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
Generating truly blank cells | Excel Worksheet Functions | |||
Blank cells represented in area charts | Charts and Charting in Excel | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |