Re: find and replace Excel 2007

Tech-Archive recommends: Fix windows errors by optimizing your registry


Thanks for the suggestions. The range test showed not to be a problem. But
when I did the =len(a1) I did get 0 in that cell. That means there are
characters behind, right? Can these me removed all at once?

"Dave Peterson" wrote:

Just some guesses...

Edit|replace (or ctrl-h) is limited to the usedrange. So if your data doesn't
go down far enough--or not far enough to the right, then excel may not find
anything to change.

You can check the used range by hitting ctrl-end. If that takes you past what
you need, then this isn't the problem.

But if it doesn't take you far enough down and to the right, you can extend the
used range by typing something in that bottom right cell (and finish the entry
with an enter). Then hit the delete key to clear the contents of that cell.
Test to see if it worked by hitting ctrl-end.

My other guess is that you have something in those cells. It could be space
characters or even those non-breaking HTML codes (char(160)'s).

If you use a formula like:
=len(a1)
(where A1 is one of the offending cells)
do you see 0?

I saved this from a previous post. You may find it useful:

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
Active***.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Lynne wrote:

I get a message saying "Excel can not find any data to replace." I have
never had this problem occur in past years but this is the first year of
using 2007 for this calculation. I am selecting a range Col. D to P. and a
varying number of rows. It will only make the replace in the first two
columns. i.e. 16 replacements were made when the range held about 60 or 70
blank cells. When I try to select any columns past these, I get the message
above. I can't select individual columns or ranges. This is occurring on
several different workbooks. Any other ideas would be much appreciated.

"Sheeloo" wrote:

I don't think there is any difference between 2003 and 2007 as far as this
behavior is concerned...

You must have at least one non-blank cell in the range for your method to
work.... If all are blanks then you can simply type 0 and press CTRL-ENTER
after selecting the range...

-Sheeloo
-------------------------------------
Pl. click 'Yes' if this was helpful...

"Lynne" wrote:

In 2003 I used to be able to select a range that contained blank cells and
use find and replace to change the blanks to 0 for an array formula to work.
Now 2007 says it can't find any matching data if I leave the find field
blank. Any ideas? There are alot of empty cells that need a 0 value!

Thanks

--

Dave Peterson

.

Quantcast