# Microsoft Office Application Help - Excel Help forum > Excel General >  >  find and replace not working

## kitbit

Hi All,

Sorry for the basic question...
I'm using Excel 2007 and trying to replace about 2000 commas with full stops. I want to use find and replace but keep getting a message saying that Excel cannot find the data I'm searching for. 

Excel help suggests I haven't clicked on "find" before clicking "replace" but I did. 
I've tried highlighting the column in which I want to make the replacements, but same message. 
Also tried highlighting nothing, same message.
Tried copying and pasting the whole thing into a new file, but same message.

What am I doing wrong?!

TIA

----------


## Andy Pope

Check the Options on the Find/Replace dialog.

make sure 'Match entire cell contents' is not enabled

----------


## kitbit

Just checked it - it isn't. There is also no other formatting specified.

----------


## Andy Pope

can you post small example

----------


## kitbit

Thanks Andy, do you mean a screenshot? I've attached my last attempt. Column B has currency in euro format ie "1.000,00" and I need to change it to "1000.00". Am trying to change the "." to a space, then change the comma to a point, then change the space to a comma. Phew. 

Here, column B is highlighted before doing the find.

----------


## Andy Pope

No I meant a workbook so we could see what was actually in the cells.

Nut using your picture can you confirm that the cell B2 contains 1.901,23
and that the decimal place and or comma are not part of the number format.

----------


## arthurbr

I noticed you use Look in.. Formulas
Is this correct?

----------


## kitbit

Andy, I've attached a sanitized version to omit sensitive data, have just tried the same thing with this version and it still has the problem.
(I am nearly certain it will be user error. )
The file has been saved as .xls and as .xlsx, same problem in each. I attach the .xls version.

arthurbr, sorry I'm not sure what you mean? Can't see a "look" funcion or option within the "find"?

----------


## kitbit

forgot - column B is formatted to be currency, yes, but even when it isn't the problem persists (same throughout rest of workbook in its complete form, including text).

----------


## davesexcel

Worked fine for me,
I hit Ctrl & H
Find  ,
replace  nothing
Then hit replace all, I did not use the options window.
Once the action was completed, 1000's separators appeared, you can get rid of those by just formatting the cells.

----------


## Andy Pope

Actually the screen shot was useful!

You have formatting set. Notice the 2 boxes that say Preview. This means you have some format set that does not match.

I had no trouble do the required change until I added Bold font to the find parameter. This then raised the error warning you described.

Either remove the format part or check that the cells match specification.

----------


## kitbit

Really?? 
Have just tried it again on a text column in the original sheet, and I still get the error. Also tried it in the sanitized version, and I still get the error. 
Does this mean I have a setting somewhere that is causing the problem?

----------


## kitbit

(crossed posts with Andy)

Andy, I just tried on the original and checked the formatting matched what I was searching for. It did - and still I have the error. 
agh!

----------


## Andy Pope

As with the others your test file worked.

I assume if you remove the formatting part it works for you?
If so we need to know what formats you applied

----------


## kitbit

There is a "paid in/paid out" column which has blue and red font colouring accordingly. I did a find/replace there earlier (which worked) which is possibly why the options setting is still showing on the find/replace dialogue box?

The test I just did was to go to another column with the default formatting and to search for the word "Completed" which is clearly visible in many of the cells. (screenshot attached). Although the "options" are minimised, they are all set to default. Is there anything else I can provide?

----------


## Andy Pope

Not sure what to suggest next as F/R is working for us when testing. 

Can you share a cut down version of the real file offline?   
andy AT andypope DOT info

----------


## kitbit

I'm going to close down, reopen, double check and try it again first! If it's working for you it must be something in my settings or something I'm doing, so will go through everything locally first. If I still get the error then yes please, I'll send through a version! (if I find the prob obviously I'll come back and update too)

thanks very much so far!

----------


## kitbit

I closed everything and shut down Excel.
I restarted it and opened a spreadsheet I know was OK last time I used it, and yes it was still OK with the find/replace.
So I opened this one again...and this time it works perfectly well. 

I don't get it, however I accept I must have unwittingly changed a setting that affected the search and therefore "resetting" Excel has done the trick.

Thanks very much for all your help and time, it's hugely appreciated  :Smilie:

----------


## Andy Pope

that's life! glad you are sorted now.

----------


## dskeens

Fast forward to 2013. My "find" has suddenly stopped finding. I've messed with Options and Formatting to no avail. And I can't get my screenshot to show here. Dang!  Next to "find what?" the next box WAS reading "PREVIEW* and now has "no format set" and no option to change it. I have meessed with this for too long today. Any suggestion???

----------


## arlu1201

dskeens,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## periwinkle

I have a problem with the find & replace feature as well.  It does not work all the time.  I cannot provide screenshots either, as the information I work with is confidential.  What I can say is the feature is finnicky and works about 2/3 of the time!  I don't know why this is either.  Sometimes I have just seen the name I'm searching for, and I open the "find" feature and search for it, and Excel will claim is not in the spreadsheet.  Then I scroll to the name and type it into the "find" feature again, and suddenly, Excel recognizes that yes, it actually does have that name in the list!!!!  It is a persnickety thing, and I don't know why Excel has this bug.  I'm pretty sure I'm not doing anything wrong.  As I said, Excel will claim that it cannot find the name, but I KNOW it's there.  I just don't know where in the list.  It's frustrating as the "find" feature only works some of the time.

----------


## tbcha1

if your find does not work what may be the issue is you have the find format feature on
to turn it off:

 = cntrl F
 = expand the options
 = click Format button
 = Find Format box will open up. go to each tab and click the Clear button at the bottom right
 = click ok 

use the find box again and see if it works. If not then i dunno what could be wrong  :Smilie: 
but it worked for me

----------


## BigDawg15

I know this is a really old post but after a google search, Andy Popes suggestion of checking that
the match all contents was not checked fixed my problem.  Couldn't find a solution prior.

Thanks so much,

BigDawg15

----------


## Yamamoto

The solution in my case (after trying all sorts of ideas) was to click  "Find what" -- "Format" drop down button and select "Clear Replace Format", and to do the same for "Replace with".  Even though my cells were formatted as General and I had specified to find General, Find didn't work.

----------


## FDibbins

Thanks for sharing, Yamamoto  :Smilie:

----------

