See the attached worksheet. What I WANT to do is, in columns A and B, do an Edit/GoTo/Special/Blanks, CtrlEnter to select all, =, up arrow, enter to paste the value above in all of the blank cells. The problem is all of my blank cells appear to be non-blank. I've tried using the TRIM function on them, the CLEAN function, copy and paste as values, etc. Can anyone help me to replace the non-empty blank cells with empty blank cells? Thanks in advance.
Last edited by jomili; 12-10-2010 at 01:57 PM.
Hi
Your example seem to have gone astray, please re-attach.
Regards
Jeff
Whoops! Attached now.
You have spaces in those cells which since they are stand alone won't be removed with TRIM.
Select Column B (for example) and CNTRL H to call up Search and Replace
Pick Options and make sure "Match Entire Cell Contents" is picked.
Find (enter a space)
Replace (enter nothing)
Then "Replace All"
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Chemist,
Good try, but unfortunately that was among the "etc" things I tried (but I hadn't tried the "Match Entire Cell Contents"; didn't know about that), and it still doesn't work. No results found. Were you able to make this work on the sample I pasted?
Yes, I had over a thousand matches in Column B. I was then able to GO>Special>Blanks
Last edited by ChemistB; 12-10-2010 at 12:08 PM.
In a really blank cell, type =CODE(B12) where B12 is the cell you're interested in finding out what's really in there. If you get a 32 that means there is a space in that cell.
Hmmm, curiouser and curioser. I just went back and checked my example, and your solution works perfectly on it. I tried my source document, and it wouldn't work (no results found). But my example was simply a copy of the sheet from my source document, with a few name changes. Why would it work in one and not the other?
Try the code thing and see if it returns something other than 32
Chemist,
I tried the CODE(A1) thing on my "blank" cells in my source document. I get a "Value" error. For cells that ARE filled (and appear filled) I get values such as 82, 49, etc. No 32's are found.
Hmmm, #Value means there are no hidden characters in those cells. 82 is capital R, 49 is 1.
Definitely curiousier. Can you save your original spreadsheet under a different name, delete all the data except Col B (I assume Col B is the problem?) and upload it?
Okay, here it is. I left the A and B columns, because I think both are having trouble, and also included columns A and B from the original source, so there are two tabs.
Okay, try this (seems to work but I don't know why). Select a column, then Data > Text to Columns >Finish. Then the other column. Try F5.
Did that work?
Okay, now that's just weird.
I tried it on column B first (delimited), so you'd think if there was anything EXTRA in the cell it would populate column C, but column C stayed just the way it was. Then I did column A, which should have wiped out B, but didn't. But sure enough, after that I could do my pastespecial routine.
Why would that happen, and how did you come up with that solution?
First, since you didn't have anything which would break up the column into multiple columns (i.e. Tabs), it really just copies the new column onto the old column. This is a good way to reset a column when it's not working right and you can't figure out why. Once we figured out that there really weren't any characters in the cells as far as we could tell, I figured that was worth a shot. Glad it worked.
I'm glad, too. Thanks for your help and sticking with me through this. Now maybe I can get some work done.
Thanks, and a Merry Christmas to you!
John
Merry Christmas
I have had a similar issue. I have needed to use the Find > Go To Special > Blanks feature to select all blank cells in a column where I entered an =IF(True, False, "") formula that either provided a particular result on true or "" blank if FALSE. Then I would Copy > Paste Values.
I decided to instead of using "" to return blank, that I would have the false result literally ="Blank" spelled out. Then, I could Copy > Paste Values and then Find "Blank" and Replace with nothing so that the cells are truly blank and prepared for the Go To Special > Blanks feature.
Welcome to the forum.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks