CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH
 

IN EXCEL, HOW DO I SHOW A CELL THAT IS EMPTY AS HAVING IT FILLED WITH SPACES
AND HAVING A PARTICULAR LENGTH (FOR EXAMPLE 35). I NEED THIS FOR A CLIPBOARD
AND IF THE DOESN'T COUNT THE SPACES, IT THROWS OFF THE POSITION OF THE NEXT
CELL

I'm not familiar enough with macros. Is there another way?

"CHARI" wrote:

IN EXCEL, HOW DO I SHOW A CELL THAT IS EMPTY AS HAVING IT FILLED WITH SPACES
AND HAVING A PARTICULAR LENGTH (FOR EXAMPLE 35). I NEED THIS FOR A CLIPBOARD
AND IF THE DOESN'T COUNT THE SPACES, IT THROWS OFF THE POSITION OF THE NEXT
CELL

Well, you may consider using the function REPLACE.
1. Leave the column "A" empty.
2. In cell B1 type: =REPLACE(A1,1,10,"xxxxxxxxxx...")
*** where instead of the xxxxx you will type 35 spaces !
3. Drag (copy) that formula down to where ever you need.
4. Select the WHOLE range in column "B" and pres Ctrl+C (Copy)
5. Move to cell C1 and in the main menu: Edit Paste Special mark the
redio button "Values" OK.
*** Column "C" has 35 spaces in each Cell.
Please forgive my bad English as it is not my mothers tounge.
Michael Avidan - ISRAEL
http://forums.tapuz.co.il/office

"CHARI" wrote:

I'm not familiar enough with macros. Is there another way?

"CHARI" wrote:

IN EXCEL, HOW DO I SHOW A CELL THAT IS EMPTY AS HAVING IT FILLED WITH SPACES
AND HAVING A PARTICULAR LENGTH (FOR EXAMPLE 35). I NEED THIS FOR A CLIPBOARD
AND IF THE DOESN'T COUNT THE SPACES, IT THROWS OFF THE POSITION OF THE NEXT
CELL

Or the somewhat shorter

=REPT(" ",35)

although I doubt it is a good idea to use spaces for layout as the OP wants

--
Regards,

Peo Sjoblom

(No private emails please)


"מיכאל (מיקי) אבידן" wrote in message
...
Well, you may consider using the function REPLACE.
1. Leave the column "A" empty.
2. In cell B1 type: =REPLACE(A1,1,10,"xxxxxxxxxx...")
*** where instead of the xxxxx you will type 35 spaces !
3. Drag (copy) that formula down to where ever you need.
4. Select the WHOLE range in column "B" and pres Ctrl+C (Copy)
5. Move to cell C1 and in the main menu: Edit Paste Special mark the
redio button "Values" OK.
*** Column "C" has 35 spaces in each Cell.
Please forgive my bad English as it is not my mothers tounge.
Michael Avidan - ISRAEL
http://forums.tapuz.co.il/office

"CHARI" wrote:

I'm not familiar enough with macros. Is there another way?

"CHARI" wrote:

IN EXCEL, HOW DO I SHOW A CELL THAT IS EMPTY AS HAVING IT FILLED WITH
SPACES
AND HAVING A PARTICULAR LENGTH (FOR EXAMPLE 35). I NEED THIS FOR A
CLIPBOARD
AND IF THE DOESN'T COUNT THE SPACES, IT THROWS OFF THE POSITION OF THE
NEXT
CELL

Chari

You should become familiar with macros if you intend to utilize all the power
of Excel.

Visit David McRitchie's site on getting started.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

There are other ways..........

In the empty cell enter this formula =REPT(CHAR(32),35)

This will enter 35 spaces in the cell.

Now copy the cell and Paste SpecialValuesOKEsc.

The 35 spaces will remain.

OR, simply enter 35 spaces in a cell then copy that cell to other blank cells.

As you can see, the macro would be much easier......no formulas, no copying
and pasting.

To use the macro given by Michael......with the worksbook open, hit ALT + F11
the CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the lines of code in there.

ALT + Q to go back to Excel then ToolsMacroMacros.

Select the macro and "Run" on any empty cell you have selected.

When/if happy, save the workbook.

For improvments, I would suggest this change in code.

Sub SP()
For Each rcell In Selection
rcell.Value = Space(35)
Next rcell
End Sub

Stick that into the module instead of Michaels's original code.

Select the empty cells, either manually one at a time using CTRL and point or
select a range then F5SpecialBlanksOK.

Run the macro on that selection.


Gord Dibben Excel MVP

On Mon, 29 Aug 2005 14:47:14 -0700, "CHARI"
wrote:

I'm not familiar enough with macros. Is there another way?

"CHARI" wrote:

IN EXCEL, HOW DO I SHOW A CELL THAT IS EMPTY AS HAVING IT FILLED WITH SPACES
AND HAVING A PARTICULAR LENGTH (FOR EXAMPLE 35). I NEED THIS FOR A CLIPBOARD
AND IF THE DOESN'T COUNT THE SPACES, IT THROWS OFF THE POSITION OF THE NEXT
CELL