G
Gerrym
Cell appears to have nothing in it, but the result of
ISBLANK() is FALSE. Why is this.
ISBLANK() is FALSE. Why is this.
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Ron de Bruin
Hi Gerrym
maybe a space
In a other cell enter
=len(a1) to see if it is empty
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
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
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.
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.
message news:[email protected]...-----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
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
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.
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
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.
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.
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
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.
H
Harlan Grove
....Dave Peterson said:You can see the "hidden" apostrophe (prefixcharacter) by:
tools|options|Transition tab|checking Transition Navigation key.
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.
J
Jon Peltier
So use IF(LEN(A1)=0 instead of IF(ISBLANK(A1).
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
H
Harlan Grove
....Jon Peltier said:So use IF(LEN(A1)=0 instead of IF(ISBLANK(A1).
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="",...) ?
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/
_______
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.