Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
I am using Excel 2007 and returning data into a table using xfODBC, which
returns a number of blank cells. If I do an ISBLANK on the blank cells, it returns FALSE, ISTEXT returns TRUE. I want to assign a new value to the blank cells, but since Excel is considering it text, the formula would screw up legitimate values also. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
Do not test a cell with ISBLANK(). ISBLANK() will return TRUE ONLY if the
cell is truly empty. If the cell contains: ="" ISBLANK() will return false. It is better to test with: =IF(A1="","the cell is relatively empty","the cell is not empty") -- Gary''s Student - gsnu200856 "OperationsNETTC15" wrote: I am using Excel 2007 and returning data into a table using xfODBC, which returns a number of blank cells. If I do an ISBLANK on the blank cells, it returns FALSE, ISTEXT returns TRUE. I want to assign a new value to the blank cells, but since Excel is considering it text, the formula would screw up legitimate values also. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
Hi
It could be a character which looks like normal space, isblank will return False and istext will return True.Its actually a html non breaking space. Alt-0160 Goto Edit Replace Findpress the Alt key and type 0160 on the numeric keypad, type nothing in Replace. That should remove all those characters. HTH John "OperationsNETTC15" wrote in message ... I am using Excel 2007 and returning data into a table using xfODBC, which returns a number of blank cells. If I do an ISBLANK on the blank cells, it returns FALSE, ISTEXT returns TRUE. I want to assign a new value to the blank cells, but since Excel is considering it text, the formula would screw up legitimate values also. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
I used that formula and it returned "the cell is not empty"
I tried to replace Alt-0160 and it did not find any, unfortunately.... It's a mystery. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
HI
We would need a sample of your file. If you could upload a sample with your problem to a site like this one;http://www.filedropper.com/ and give us the link. Someone should be able to help you. Regards John "OperationsNETTC15" wrote in message ... I used that formula and it returned "the cell is not empty" I tried to replace Alt-0160 and it did not find any, unfortunately.... It's a mystery. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
Did you try selecting the data range (Ctrl+A) and copy . Select a new sheet.
Select A1.Right click PasteSpecialValues..now try your formulas If this post helps click Yes --------------- Jacob Skaria "John" wrote: Hi It could be a character which looks like normal space, isblank will return False and istext will return True.Its actually a html non breaking space. Alt-0160 Goto Edit Replace Findpress the Alt key and type 0160 on the numeric keypad, type nothing in Replace. That should remove all those characters. HTH John "OperationsNETTC15" wrote in message ... I am using Excel 2007 and returning data into a table using xfODBC, which returns a number of blank cells. If I do an ISBLANK on the blank cells, it returns FALSE, ISTEXT returns TRUE. I want to assign a new value to the blank cells, but since Excel is considering it text, the formula would screw up legitimate values also. Any ideas? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
Assuming A1 is one of your "blank" cells, put this in another cell to see
the ASCII (or ANSI) code for the first character (there could be more than one, but we need a starting point) and let us know the number it displays... =CODE(A1) -- Rick (MVP - Excel) "John" wrote in message ... HI We would need a sample of your file. If you could upload a sample with your problem to a site like this one;http://www.filedropper.com/ and give us the link. Someone should be able to help you. Regards John "OperationsNETTC15" wrote in message ... I used that formula and it returned "the cell is not empty" I tried to replace Alt-0160 and it did not find any, unfortunately.... It's a mystery. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
I tried that just now and ISBLANK still returns FALSE
"Jacob Skaria" wrote: Did you try selecting the data range (Ctrl+A) and copy . Select a new sheet. Select A1.Right click PasteSpecialValues..now try your formulas If this post helps click Yes --------------- Jacob Skaria "John" wrote: Hi It could be a character which looks like normal space, isblank will return False and istext will return True.Its actually a html non breaking space. Alt-0160 Goto Edit Replace Findpress the Alt key and type 0160 on the numeric keypad, type nothing in Replace. That should remove all those characters. HTH John "OperationsNETTC15" wrote in message ... I am using Excel 2007 and returning data into a table using xfODBC, which returns a number of blank cells. If I do an ISBLANK on the blank cells, it returns FALSE, ISTEXT returns TRUE. I want to assign a new value to the blank cells, but since Excel is considering it text, the formula would screw up legitimate values also. Any ideas? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
Assuming A1 is one of your "blank" cells, put this in another cell to see
the ASCII (or ANSI) code for the first character (there could be more than one, but we need a starting point) and let us know the number it displays... =CODE(A1) -- Rick (MVP - Excel) "OperationsNETTC15" wrote in message ... I used that formula and it returned "the cell is not empty" I tried to replace Alt-0160 and it did not find any, unfortunately.... It's a mystery. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
039617 48
32 32 039512 48 32 32 32 039677 48 32 32 32 32 32 32 039579 48 32 32 039736 48 32 "Rick Rothstein" wrote: Assuming A1 is one of your "blank" cells, put this in another cell to see the ASCII (or ANSI) code for the first character (there could be more than one, but we need a starting point) and let us know the number it displays... =CODE(A1) -- Rick (MVP - Excel) "OperationsNETTC15" wrote in message ... I used that formula and it returned "the cell is not empty" I tried to replace Alt-0160 and it did not find any, unfortunately.... It's a mystery. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
I solved my dilemma.
IF($B2=48, $A2, "000000") Code() did the trick for me, thank you! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell not really blank
I'm not entirely clear on what is in your cell or whether the formula you
posted is what you really need or want. The CODE function (which is returning 48 for the entry 039617) is giving you the code value for the leading 0... do all your non-blank cells start with 0? Also, you are showing 32 for the code your "blank" cells... 32 *is* the code for a blank space; so your "blank" cells are not really empty, they contain at least one blank character (I guess there could be more). You could one of two things (besides what you showed us for your solution)... one, clean up your data to remove the blank characters or, two, you should be able to eliminate the helper column (the one with the CODE function in it) and use this instead... =IF(TRIM($B2)="","000000",$A2) Doing the test this way will allow entries in your Column A cells that don't start with a 0. This, of course, assumes the "blank" cells in Column A have one or more spaces in them and no other non-printable characters. -- Rick (MVP - Excel) "OperationsNETTC15" wrote in message ... I solved my dilemma. IF($B2=48, $A2, "000000") Code() did the trick for me, thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
returning blank when reference cell is blank | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
how to get excel to display blank if reference cell blank | Excel Worksheet Functions | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions |