I have a spreadsheet with data in all different rows.
I'm trying to delete all the blank cells in between.
I tried go to special, blank, and delete cells,
but for some reason, the excel doesn't select all the blank cells.
It only selects a few blank cells, which is really strange.
Does anybody know what could be the problem?
or if someone would take a look at my spreadsheet and help me out.
Thanks a lot!
Hi,
Sounds like they are not actually blank, select one and try this formula
=ISBLANK(A1) where A1 appears to be blank but isn't will return FALSE
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Also u can try
1. Perss F5
2. Special> Blank >OK (it would select blank cells within the range)
3. RightClick>Delete (CTRL+"-")
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
Hi, thanks for your reply.
A1 is not blank.
I tried this formula in a different cell.
=ISBLANK(B6) and it turned 0
So, does it mean that cell is blank or is it not?
If it returns 1=TRUE, 0=FALSE
Originally Posted by ContaminatedWitExcel
Hi, Thanks for the suggestion. I just tried it.
It is just like when I did , Find&Select, Go to Special, Blank, Ok, and Delete Cells. It didn't select all the blank cells.
Why not post the workbook or part of it so we can see the problem?
Ok, so that means these cells that it didn't select are not truely blanks?
They sure appear to be blank.
Is there anyway to fix it?
It's a large data with 10,000+ rows.
I dont know what to do if I can't delete all the blank cells.
Thanks soooo much!
But I have a dumb question,
how can I post the file on here?
Like I said let's see a part of it?
Oops..I didn't know there's an additional option that I can post attachments. lol
Okay. So the attachment is part of the file that i'm working on.
I'm working on Sheet2 which I copied and pasted from Sheet1.
Please take me look at it for me.
Thank you!
Ok
try this one. Take a look at this pic.
Last edited by contaminated; 06-29-2009 at 05:02 PM.
Thanks for the suggestion.
Excel says can't find what i'm searching for when I type double quotation mark.
If you look on sheet1 you have some formulas, fro example in C8
=IF(LEFT(A8,4)="Name",MID(A8,FIND(" ",A8)+2,20),"")
The problem is this is putting a space in the cell - therefore it isn't blank, it needs to be
=IF(LEFT(A8,4)="Name",MID(A8,FIND("",A8)+2,20),"")
In fact if you do a FIND/REPLACE for " " to replace with "" and check look in Formula's there are over 26000 of them
I've got it
Leave FindWhat field empty. I mean just press Ctrl+F and press Find All. I've got it workin'
i cant see a difference there old chippy ! but if you do len(f10) it comes up 0
but i can see a single ' in the cell
Last edited by martindwilson; 06-18-2009 at 04:38 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Wow..that's the problem.
Thank you so much.
You solved my problem.
ok you've all lost me changing to
=IF(LEFT(A8,4)="Name",MID(A8,FIND("",A8)+2,20),"")
from
=IF(LEFT(A8,4)="Name",MID(A8,FIND(" ",A8)+2,20),"") means it wont find the space and the mid function will be wrong surely?
result 2 gives
"3631 U. S. Highway #90-East"
formula 1 gives
"ne 1 3631 U. S. Highway #90-East"
Whoops - have I got it wrong, I must admit I didn't check first - Yes I did - sorry!
Last edited by oldchippy; 06-18-2009 at 04:48 PM.
what i can understand is...insert a new sheet
select A1 hit delete just in case make sure cell formated general (just in case again)
isblank A1 shows true.. .....expected
len(a1) =0 ....expected
formula bar shows nothing visible....expected
now go copy a cell with a formula in that looks blank
and paste special values into a1 of new sheet
isblank(a1) turns to false
len(a1) still = 0 though
and in the formula bar i can see a single ' which i can delete with delete or clicking in formula bar and using backspace isblank(a1) now goes back to true
but i cant do it by find/replace nothing
copy cell into find replace either
and code(a1)shows #VALUE!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks