ISBLANK

G

Gerrym

Cell appears to have nothing in it, but the result of
ISBLANK() is FALSE. Why is this.
 
R

Ron de Bruin

Hi Gerrym

maybe a space

In a other cell enter
=len(a1) to see if it is empty
 
N

Norman Harker

Hi Gerrym

Possibilities:

The target cell contains a formula that is returning ""
The target cell contains a space
 
J

jeff

Hi,

Maybe a space? or other nonprintable character.

check =len(A1)

you can rid spaces with =Trim(A1)

jeff
 
G

Gerrym

When I do Len(a1) it returns 0. This is data downloaded
from AS/400 as an excel sheet. there are two columns, some
cells appear empty in col B, what I want to do is put the
value from the corresponding cell in Column A into Col B
is B is empty.


-----Original Message-----
Hi Gerrym

maybe a space

In a other cell enter
=len(a1) to see if it is empty

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gerrym" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
R

Ron de Bruin

If there is a ' in the cell Excel will not count it with the Len function.

Try to use this macro to clean up your cells

Select the cells and run this macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
H

Harlan Grove

If there is a ' in the cell Excel will not count it with the Len function.
Click to expand...

There are cases in which there's no ' in the cell, LEN(cell) = 0, and still
ISBLANK(cell) = FALSE. Enter ="" in a cell, copy, and paste special as value to
find one example. Nasty buggers.
Try to use this macro to clean up your cells

Select the cells and run this macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Click to expand...
...

While the macro would work, it's not necessary. Select the range containing the
problem cells. Edit > Replace, in 'Find what' clear any existing entry, hold
down an [Alt] key and using the Numeric Keypad enter 0160, clear the 'Replace
with' entry, and click the 'Replace All' button. Then with the range still
selected, run Data > Text to Columns, choose Fixed Width in the first dialog and
*immediately* click the Finish button.
 
D

Dave Peterson

You can see the "hidden" apostrophe (prefixcharacter) by:
tools|options|Transition tab|checking Transition Navigation key.

But I'd toggle that setting back to off after you've seen it.

And I clean those up by doing two Edit|Replaces.

Edit|Replace
replace: (nothing)
with: $$$$$ (some unique set of characters)

then reverse them:
replace: $$$$$
with: (nothing)

It has the advantage that you can do more than one column at a time.


Harlan said:
If there is a ' in the cell Excel will not count it with the Len function.
Click to expand...

There are cases in which there's no ' in the cell, LEN(cell) = 0, and still
ISBLANK(cell) = FALSE. Enter ="" in a cell, copy, and paste special as value to
find one example. Nasty buggers.
Try to use this macro to clean up your cells

Select the cells and run this macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Click to expand...
..

While the macro would work, it's not necessary. Select the range containing the
problem cells. Edit > Replace, in 'Find what' clear any existing entry, hold
down an [Alt] key and using the Numeric Keypad enter 0160, clear the 'Replace
with' entry, and click the 'Replace All' button. Then with the range still
selected, run Data > Text to Columns, choose Fixed Width in the first dialog and
*immediately* click the Finish button.
Click to expand...
 
H

Harlan Grove

Dave Peterson said:
You can see the "hidden" apostrophe (prefixcharacter) by:
tools|options|Transition tab|checking Transition Navigation key.
Click to expand...
....

I could quibble that if the cell's .PrefixCharacter property is "" then it's
inappropriate to call this a prefix character. Odd that Transition
Navigation Keys rather than Transition Formula Entry makes these visible,
but maybe that's just me. And despite the fact I use both 123 and Excel
every day, I've never used Transition Navigation Keys.

Using Edit > Replace with a dummy string has the one theoretical danger that
the dummy string could appear already in some cell's contents. The Text To
Columns approach is safer if slower.
 
D

Dave Peterson

It may not be safer if there are "text" numbers in that column. When you're
done, they'll be number numbers. Or if you have cells that have character by
character formatting.

I've been pretty lucky with my choices of dummy strings.
 
H

Harlan Grove

Dave Peterson said:
It may not be safer if there are "text" numbers in that column. When
you're done, they'll be number numbers. Or if you have cells that have
character by character formatting.
Click to expand...
....

Enter just ' in A1, ="" in A2, copy A2 and paste special as values into
A3. Select B1:B4, type =ISBLANK(A1) and press [Ctrl]+[Enter]. B1:B4 should
be {FALSE;FALSE;FALSE;TRUE}. Select A1:A4, Edit > Replace nothing with $$$$,
then Edit > Replace $$$$ with nothing. B1:B4 should now be
{FALSE;FALSE;TRUE;TRUE}. A1 is unchanged. Copy A2 and paste special as
values into A3. Select A1:A4, Data > Text to Columns, select Fixed Width and
click Finish. B1:B4 should now be {TRUE;FALSE;TRUE;TRUE}.

Edit > Replace can't remove hard prefix characters (those displayed in the
formula bar no matter what the Transition Navigation Keys setting may be).
Data > Text to Columns can.

I'll leave it at different tools for different tasks.
 
H

Harlan Grove

Jon Peltier said:
So use IF(LEN(A1)=0 instead of IF(ISBLANK(A1).
Click to expand...
....

What would be the benefits of using IF(LEN(x)=0,...) rather than
IF(x="",...) ?
 
J

Jon Peltier

Jon Peltier said:
Harlan Grove wrote:

What would be the benefits of using IF(LEN(x)=0,...) rather than
IF(x="",...) ?
Click to expand...

Harlan -

Got me there. Either works, and x="" requires fewer keystrokes.

A long time ago, I remember reading that in VBA testing for the length
of a string was quicker than testing whether the string was "". I don't
remember the source, and I never tested it, yet reading it may have led
to the habit of using LEN(x)=0 in my If statements.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Top