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

C

CHARI

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
 
P

Peo Sjoblom

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)
 
G

Gord Dibben

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 Special>Values>OK>Esc.

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-click>Insert>Module.

Paste the lines of code in there.

ALT + Q to go back to Excel then Tools>Macro>Macros.

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 F5>Special>Blanks>OK.

Run the macro on that selection.


Gord Dibben Excel MVP
 
Top