Replace empty cells with blank space

**charles.2345** · 06-23-2008, 03:27 PM

Hi all,

I have a question... I have many sheets in my workbook with many filled or unfilled cells... I want that any cell in my whole workbook should be filled with " " ie a space if it is currently blank (ie don't touch the cells which have some data) and as there are many cells in a sheet .. I don't want to fill them with spaces as the file size will increase... I just want to do this for cells A-1 to AB-200.

What is the way of doing this with a formula or programming ie without macros... (or is macro the only way?) What if I just want to do this to a sheet and not to the whole workbook.

Thanks folks,

Charles.

**NBVC** · 06-23-2008, 03:48 PM

Please read our forum rules and then amend your title to a more appropriate one.

Thanks.

**TIPPYS** · 06-23-2008, 03:52 PM

then have a look at the first video on this site.

http://www.contextures.com/xlVideos01.html#FillBlanks

**charles.2345** · 06-23-2008, 10:15 PM

Hi,

I could not read the rules (when I clicked on the URL in your reply) I was sent to page http://www.excelforum.com/showthread.php?t=642322 and it said... you are not authorized for this page..

How can I change the title of my thread now?

Either ways, Could someone please let me know the formula for the above??

Best regards,
Charles.

**NBVC** · 06-24-2008, 07:52 AM

I fixed your title for you... Our Administrator will look into the Rules thread and getting access.

In the meantime, see suggestiion above from TIPPYS

**charles.2345** · 07-18-2008, 08:12 PM

Hello all,

I looked at the video but it is to fill the blank cells with values from the first non-blank cell above (ie in the same column) BUt I want to fill just one space in the cell. How to do this??

Thanks,
Charles.

**martindwilson** · 07-18-2008, 09:45 PM

just select whole range a1 to ab200
follow video but instead of typing = and selecting cell above
just type ="" then ctrl+enter or if you just want space hit space bar and then ctrl+enter

**charles.2345** · 07-28-2008, 11:40 PM

Thank you so much Martin. It worked well. I have recorded a macro for this in Personal.xls But how can I do this for all the sheets in my workbook at one go??

Also I want Excel to do this automatically for every column or row that I insert or if I add a new sheet then all cells in the range A1 to Z500 should have a space... Actually I want that any cell in excel should have a space so that the previous cell's data does not show in the next cell (I dislike this and giving a space in the next cells seems to be the only way to hide it) But I am limiting the range to A1 to Z500 as space-in-all-empty-cells would make the workbook large in size... Please tell me the answers to these questions.

Thanks again,
Charles.

**martindwilson** · 07-29-2008, 12:13 PM

why not just format cells as wrap text then they wont over lap in other cell when viewed?

**charles.2345** · 08-02-2008, 04:36 PM

Wrapping will increase the row height. I dont like that.. I like them all being 12.75. They should have this option of what effect i achieve with giving space in the next cell, without having to give that space... its a special view where the text just hided 'under' the next column.. if the text is 'Right aligned' it should hide under the column to the left. So wrap is not what i am looking for.

**martindwilson** · 08-02-2008, 08:31 PM

try this macro (insert module)

Please Login or Register  to view this content.

replace $a$1:$d$10 with your range
run macro as required
or in "this work book"

Please Login or Register  to view this content.

Replace empty cells with blank space

LinkBack

Thread Tools

Rate This Thread

Display

Replace empty cells with blank space

Coudn't read the rules page and how to edit the title now?

Still not solved..

Now the question arises....

wrapping is not what I want

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions