ignoring empty???

Hi all,

one of my students presented me with the following:

"I get a list from another application.
There is a column that has either value "yes" or looks like empty.

Now, when I want to jump to the next row that has value "yes" I use the 
shortcut key Ctrl+ArrowDown.
However, sometimes when a cell looks like empty there may be a space, 
with the result that when I need to jump to the next "yes" cell I end up 
in a cell that has a space. (one that looks empty)
So, I used the function Trim to get rid of all the superfluous spaces in 
an extra column. And also pasted the result as values.
But now the strangiest thing occurs:
When I use Ctrl+Arrowdown my selection immediately jumps to the last 
cell in the hole column, where you would expect the selection to be the 
next cell with a "yes".
And when you check the now looking like empty cells they really are 
completely empty, but someway or another Excel still doesn't regard them 
as empty. with the result that you immediately jump to the last row, 
instead of to the next not-empty cell.

I have have made a few checks myselfe and experienced the same thing.

When you want a workaround for this problem you first need to understand 
  where the problems comes from.
I can't figure this one out.

Anybody got any ideas???


Any suggestion is greatly appreciated.


Sybolt
0
11/27/2007 8:06:27 PM
excel 39879 articles. 2 followers. Follow

7 Replies
818 Views

Similar Articles

[PageSpeed] 31

What do you get if you use:

=LEN(A15)

(A15 is just an example use one of your *empty looking* cell references)

What do you get with:

=CODE(A15)


-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"sybmathics" <startingafterthissyboltho@xs4all.nl> wrote in message 
news:474c78c1$0$233$e4fe514c@news.xs4all.nl...
> Hi all,
>
> one of my students presented me with the following:
>
> "I get a list from another application.
> There is a column that has either value "yes" or looks like empty.
>
> Now, when I want to jump to the next row that has value "yes" I use the 
> shortcut key Ctrl+ArrowDown.
> However, sometimes when a cell looks like empty there may be a space, with 
> the result that when I need to jump to the next "yes" cell I end up in a 
> cell that has a space. (one that looks empty)
> So, I used the function Trim to get rid of all the superfluous spaces in 
> an extra column. And also pasted the result as values.
> But now the strangiest thing occurs:
> When I use Ctrl+Arrowdown my selection immediately jumps to the last cell 
> in the hole column, where you would expect the selection to be the next 
> cell with a "yes".
> And when you check the now looking like empty cells they really are 
> completely empty, but someway or another Excel still doesn't regard them 
> as empty. with the result that you immediately jump to the last row, 
> instead of to the next not-empty cell.
>
> I have have made a few checks myselfe and experienced the same thing.
>
> When you want a workaround for this problem you first need to understand 
> where the problems comes from.
> I can't figure this one out.
>
> Anybody got any ideas???
>
>
> Any suggestion is greatly appreciated.
>
>
> Sybolt
> 


0
sandymann2 (1054)
11/27/2007 8:23:39 PM
"from another application"

Which would that be?

Perhaps the spaces are non-breaking html spaces.......0160 character

TRIM and CLEAN will not get rid of these.

Try Edit>Replace

What:  Alt + 0160  using NumPad

With:  nothing

Replace all.


Gord Dibben  MS Excel MVP


On Tue, 27 Nov 2007 21:06:27 +0100, sybmathics
<startingafterthissyboltho@xs4all.nl> wrote:

>Hi all,
>
>one of my students presented me with the following:
>
>"I get a list from another application.
>There is a column that has either value "yes" or looks like empty.
>
>Now, when I want to jump to the next row that has value "yes" I use the 
>shortcut key Ctrl+ArrowDown.
>However, sometimes when a cell looks like empty there may be a space, 
>with the result that when I need to jump to the next "yes" cell I end up 
>in a cell that has a space. (one that looks empty)
>So, I used the function Trim to get rid of all the superfluous spaces in 
>an extra column. And also pasted the result as values.
>But now the strangiest thing occurs:
>When I use Ctrl+Arrowdown my selection immediately jumps to the last 
>cell in the hole column, where you would expect the selection to be the 
>next cell with a "yes".
>And when you check the now looking like empty cells they really are 
>completely empty, but someway or another Excel still doesn't regard them 
>as empty. with the result that you immediately jump to the last row, 
>instead of to the next not-empty cell.
>
>I have have made a few checks myselfe and experienced the same thing.
>
>When you want a workaround for this problem you first need to understand 
>  where the problems comes from.
>I can't figure this one out.
>
>Anybody got any ideas???
>
>
>Any suggestion is greatly appreciated.
>
>
>Sybolt

0
Gord
11/27/2007 8:25:00 PM
Sandy Mann schreef:
> What do you get if you use:
> 
> =LEN(A15)
> 
> (A15 is just an example use one of your *empty looking* cell references)
> 
> What do you get with:
> 
> =CODE(A15)
> 
> 
yes, tried that, but the result stays the same.

as a matter of fact i have tried of possible workarounds, but they all 
end up in the same problem. Where I cannot understand why, and more 
cannot explain how to solve the problem.
0
11/27/2007 9:04:45 PM
Gord Dibben schreef:
> "from another application"
> 
> Which would that be?
> 
> Perhaps the spaces are non-breaking html spaces.......0160 character
> 
> TRIM and CLEAN will not get rid of these.
> 
> Try Edit>Replace
> 
> What:  Alt + 0160  using NumPad
> 
> With:  nothing
> 
> Replace all.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> 

I have tried most of your suggestions already.

But it looks like it doesn't matter where the data come from.

If you simply create in a new workbook a colum where sometimes in a cell 
you have an entry, some times you have a blank cell and sometimes you 
have a lookalike empty (with a space or spaces).
When you jump around with the key combination Ctrl+Arrowdown you would 
select the last cell with an entry so also the cells with a space. Now, 
if you remove the spaces with the trimfunction and next paste the result 
as values, you are not able to jump to the next filled cell, but you 
will end up in the last cell in the column.

I even tried saving the file and closing and then reopening, which 
sometimes helps. But no.

I'm really puzzled about this one.

Try it, Gord.

This is a strange one.


Greets,

Sybolt
0
11/27/2007 9:17:45 PM
> yes, tried that, but the result stays the same.

But what returns do you get from these formulas?

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"sybmathics" <startingafterthissyboltho@xs4all.nl> wrote in message 
news:474c866a$0$232$e4fe514c@news.xs4all.nl...
> Sandy Mann schreef:
>> What do you get if you use:
>>
>> =LEN(A15)
>>
>> (A15 is just an example use one of your *empty looking* cell references)
>>
>> What do you get with:
>>
>> =CODE(A15)
>>
>>
> yes, tried that, but the result stays the same.
>
> as a matter of fact i have tried of possible workarounds, but they all end 
> up in the same problem. Where I cannot understand why, and more cannot 
> explain how to solve the problem.
> 


0
sandymann2 (1054)
11/27/2007 9:21:08 PM
If you used:  =trim(a1) and it returned nothing (essentially ="" as a formula),
then converted these formulas to values, then this would apply:

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar.  You'll see an
apostrophe.  (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what:  (leave blank)
with:  $$$$$
replace all

Immediately followed by:
Edit|Replace
what:  $$$$$
with:  (leave blank)
replace all

sybmathics wrote:
> 
> Hi all,
> 
> one of my students presented me with the following:
> 
> "I get a list from another application.
> There is a column that has either value "yes" or looks like empty.
> 
> Now, when I want to jump to the next row that has value "yes" I use the
> shortcut key Ctrl+ArrowDown.
> However, sometimes when a cell looks like empty there may be a space,
> with the result that when I need to jump to the next "yes" cell I end up
> in a cell that has a space. (one that looks empty)
> So, I used the function Trim to get rid of all the superfluous spaces in
> an extra column. And also pasted the result as values.
> But now the strangiest thing occurs:
> When I use Ctrl+Arrowdown my selection immediately jumps to the last
> cell in the hole column, where you would expect the selection to be the
> next cell with a "yes".
> And when you check the now looking like empty cells they really are
> completely empty, but someway or another Excel still doesn't regard them
> as empty. with the result that you immediately jump to the last row,
> instead of to the next not-empty cell.
> 
> I have have made a few checks myselfe and experienced the same thing.
> 
> When you want a workaround for this problem you first need to understand
>   where the problems comes from.
> I can't figure this one out.
> 
> Anybody got any ideas???
> 
> Any suggestion is greatly appreciated.
> 
> Sybolt

-- 

Dave Peterson
0
petersod (12004)
11/27/2007 9:36:18 PM
OK, Dave.

Replacing solved the problem.

Thanks a lot.

Sybolt
0
11/28/2007 6:43:09 AM
Reply:

Similar Artilces:

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

IF to ignore #N/A
Hi, I have a string of IF conditions, which I am combining with SUM. I want to get the IF conditions to ignore any cells containg the error message #N/A. How can I do this? Thanks, Bertie. -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=398660 claytorm Wrote: > Hi, > > I have a string of IF conditions, which I am combining with SUM. I want > to get the IF conditions to ignore ...

Stop ignoring me! Emails on behalf of.
I have a rule that moves emails from 'bob@random.rnd' * to another folder. But my rule does not cope when the email comes in like "H.R.Department@random.rnd on behalf of bob@random.rnd" then the rule does not work anymore. How can I write a rule to cope with this sort of annoying email? * Email address has been changed to protect privacy use a rule that looks for words in the header - and use bob@random.rnd as the words? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Aut...

Sorting ignoring alpahnumeric lists ignoring "the" and "an".
I am trying to sort a large alphanumeric list of titles, but wish to do so igniring definitive articles such as "The" and "an". Is there anyone that can tell me how to do this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ create a dummy column using =Substitute(Substitute(substitute(A1,"the ",""),"an ",""),"And ","") then drag fill down the column. Sort on the column with the formul...

testing: please ignore
...

Deleting Empty Rows #2
I need a macro that will delete all rows that are empty. I cut and pasted data I received in a text file. The problem was that the lines in the text file were seperated by a blank line. I would like to be able to delete all these empty rows. (There's about 10,000 rows, half of which are empty!) Can anyone help with this macro. Thanks. Morris You don't need a macro. Select all by CTRL + a(twice in 2003) and F5>Special>Blanks>OK Edit>Delete>Entire row But a macro can be used if you wish. Public Sub DeleteRowOnCell() ''delete any row that has a blank in ...

Why is Cancel Ignored?
I have a situation where two fields (both Combo Boxes in this case) are optional if neither is used and required if either is used; i.e., if either one is used they must both be used. The Form BeforeUpdate property is '=CrossCheckKeys(0)'. The CrossCheckKeys function is: Function CrossCheckKeys(Cancel As Integer) Dim CurrForm As Form: Set CurrForm = Screen.ActiveForm With CurrForm If (IsNull(.Controls("comSinkKey")) And Not IsNull(.Controls("comSrceKey"))) _ Or (IsNull(.Controls("comSrceKey")) And Not IsNull(.Controls("comSinkKey...

Ignore Blanks
I have a worksheet that I copy cells from other worksheets. Just a simple copy/paste/link works great - unless the original cell is blank - and then it puts a 0% in. I need it to stay blank and not to put any value if blank but cannot figure out how to do that. I am not trying to sum or concatenate or any other forumula, simply updating various worksheets in a workbook weekly and have my master update from all the worksheets. I have over 57000 cells so I really don't want to have to manually update each cell when it's blank (not to mention I don't know since I do ...

ignore
test ...

XML generation of empty elements
I use a dataset to generate XML. By default the generated XML doesn't include empty columns. How can I control the generation so that the entire content of the dataset is comming out as XML, including the empty columns. The purpose is so that a recepient of the XML can empty or NULL a row column in a database. You have to set the AllowDBNull property on each column. see http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataColumnClassAllowDBNullTopic.asp -D "Anders Nilsson" <anonymous@discussions.microsoft.com> wrote in message news:09d301c3be3d$f0f1...

Summing #s while ignoring blanks...
Hello all, Before I ask my question I just want to thank you all in this group for your amazing helpfulness... I'd be so lost without this group! Thank you all! Now onto business... H2:H350 all have values... J2:J350 are always changing. by me inputting and deleting etc.. I want to be able to sum H2:H350 ONLY if there is a value in it's corresponding J cell... so if J2, J3, and J20 have values add H2, H3, H20. if I put a # in J4 add H4 too. I hope I worded my question in a way that makes sense... thank you all again! Try this: =3DSUMIF(J2:J350,"<>",H2:H350) H...

Sumproduct ... Empty Cells vs Spaces?
Excel 2000 ... I have data I copy/paste to Excel from an Access query (I know nothing about Access so I munipulate data in Excel) ... My data consist of 40 columns by 10,000 rows. I am using SUMPRODUCT to compare data in 6 Columns (you just gotta love this function). 3 Columns contain all data while the other 3 Columns contain Data &/or empty cells or cells with "spaces" ... (at least I assume "spaces" because they appear empty, but are not empty.) Above said ... I want SUMPRODUCT to only be TRUE when there is data in all 6 Columns ... So, how do I best wri...

Bug: Array formulas in conditional formatting ignored on XML Spreadsheet load
My Google search came up dry... Conditional Formatting rules are *supposed* to treat formulas as array formulas. But when loading an XML Spreadsheet file, conditional formatting rules are *not* treated as array formulas, thus breaking the formatting. At least in Excel 2003, haven't tested others. Workarounds I've considered: - After loading, select the the formatted range, go to Format : Conditional Formatting, and hit OK. Then it works fine, no modification to formulas needed. (not an option here, unsophisticated users) - Use the native Excel format (not an option here, server-ge...

How to ignore #NA in subtotals?
I have several data tables of identical layout that are updated daily, the first two manually and the others by preset formula that calculates from the first two. If a zero or no data are input into cells in the first two then the formula of the others will return #NA, this is intentional because charts are plotted from the latter tables and #NA prevents zero's being plotted all over it for data that has not been entered yet (zero is a valid in negative and positive data entry only when entered). Each table has a SUM total, but, if #NA exits in any one of the cells totaled the S...

Spelling and "Ignore"/"Ignore All"
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a document with a lot of seventeenth-century spellings and proper names, all of which Word is tagging as misspellings. <br><br>That's all well and good, but I keep having to click &quot;Ignore&quot; or &quot;Ignore All&quot; (I've tried both with the same results) again and again and again. For some reason, Word is not learning NOT to mark those words. <br><br>I don't want to add them to the dictionary, because some of them are too close to actual mis...

Ignore
Please ignore - testing [sent from Jim Dell] -- Jim Bunton ...

counting empty cells in a column.
Office Excel 2007 I have a file with two sheets Addresses and Summary. In a cell on the Summary sheet, I want to count the number of blank cells in Column B on the Addresses sheet that contains a Heading row. I can easily count the number of occurences of the word Westridge in Column E with this: =COUNTIF(Addresses!E2:E7061,"Westridge") =COUNTIF(Addresses!B2:B7061," ") I can't figure how to count the blank cells. Thanks. Do you want to count how many times column B has a blank/empty cell and column E of the same row has Westridge? =SUMPRODUCT(--(Addresse...

Empty cell warning #2
This is a multi-part message in MIME format. ------=_NextPart_000_0044_01CCEEF1.98A45FA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The macro below is more or less what I'm looking for except I don't want = a range but I would like to list specific cells instead. Since I don't = know how to write macros I'm hoping someone can modify this macro so I = can simply edit it by adding the cell(s) in question, for example: b1, = c7, g12 etc. So if someone presses tab, enter or arrow the message would = appear. thanks for...

SmartList is Empty #2
Hello, Smart List is empty on one of the Pcs of my network , I am working as sa user but the list is empty. Note : user have the right permission to access SmartList. Any idea ? Thanks, Which Smarlist are you running? -- Fliehigh "Amdawi" wrote: > Hello, > Smart List is empty on one of the Pcs of my network , I am working as > sa user but the list is empty. > Note : user have the right permission to access SmartList. > > > Any idea ? > > Thanks, > > Look at the selects. Also look at the number of records to display in the select windo...

I want a formula to ignore text eg 5mts * 5 ignoring the mts any .
any help to the above would be appreciated Two ways for the example posted: You could try something like this: =LEFT(I11,1)*I12 If 5mts is in I11 and 5 is in I12 You may also want to consider using a Custom Number format that would put the mts into the cell instead of typing it manually. Something like: General"mts" tj "Malshenton" wrote: > any help to the above would be appreciated ...

emptying pst file
I am trying to gain hard disk space and have deleted emails but my personal folder still shows 12 gig. There is about 2 gig which I want to keep. As best I can determine the PST file keeps stuff even though I want to get rid of it for good. How do I dump all this stuff. The 10 gig is deleted emails. Did you compact your file after you deleted items? -- Russ Valentine "yorkieshome" <yorkieshome@discussions.microsoft.com> wrote in message news:E1867F43-5911-4EFA-8A37-F2D221EE1575@microsoft.com... >I am trying to gain hard disk space and have deleted emails b...

How do I ignore newline character/carriage return while importing
I am using Excel 2003. I need to import a comma delimited file into an excel sheet. While importing into an excel sheet I need to ignore some carriage returns/newline characters. I tried using double quotes (") but this doesn't ignore newline charcters. Sample file is as follows 1,name,description details 2,name,description details 3,name,description details In the above I need to ignore the carriage retun of the 1st record and display the 'description details' in one column. AFAIK there is nothing about Excel's importing feature that provides for variable random carr...

Ignore DocType
I am reading data from an XML document with xmlDoc.load(fullname). Unfortunately, in my xml file there is the definition of a doctype file that is not available and so, it buggs. I would like to ignore it, to remove this line, or I don�t know� my language is Visual C#. Thanks in advance for your help. Nathalie *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Nat wrote: > I am reading data from an XML document with xmlDoc.load(fullname). > Unfortunately, in my xml file there is the definition of a doctype file &g...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

"Can not empty the clipboard"
When trying to copy and paste, I get the message "Can not empty the clipboard" Help! Go here and search for "cannot empty the clipboard" http://search.support.microsoft.com/kb/c.asp?ln=en-us&sd=gn Gord Dibben MS Excel MVP On Tue, 27 Apr 2010 12:08:02 -0700, Nutgrower <Nutgrower@discussions.microsoft.com> wrote: >When trying to copy and paste, I get the message "Can not empty the clipboard" >Help! Nutgrower wrote: > When trying to copy and paste, I get the message "Can not empty the clipboard" > Help! ...