Home

Excel Find not Working

%3CLINGO-SUB%20id%3D%22lingo-sub-543653%22%20slang%3D%22en-US%22%3EExcel%20Find%20not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543653%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20find%20a%20value%20in%20a%20worksheet%20and%20Find%20is%20not%20working.%20If%20I%20search%20for%20text%20it%20works%2C%20but%20when%20I%20look%20for%20a%20value%20it%20doesn't%20find%20what%20I%20want.%20Here%20is%20what%20I%20enter%3A%20%22Find%20what%3A%22%2050%2C000%26nbsp%3B%20%26nbsp%3B%22Within%3A%22%20Sheet%26nbsp%3B%20%26nbsp%3B%22Search%3A%22%20By%20Rows%26nbsp%3B%20%26nbsp%3B%22Look%20in%3A%22%20Values%20%22Format%3A%22%20Number.%20No%20other%20options.%20(I%20also%20tried%20just%20the%20number%20and%20no%20comma%2C%20but%20it%20did%20not%20work%20either.)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20weird%20is%20there%20is%20a%20button%20or%20box%20that%20says%20%22Preview*%22%20that%20doesn't%20do%20anything%20and%20I%20can't%20get%20rid%20of%20it.%26nbsp%3B%20I%20pasted%20a%20screenshot%20of%20it.%20Any%20ideas%20what%20is%20wrong%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112480i936871F9DC73C236%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Excel%20Find%20Box.jpg%22%20title%3D%22Excel%20Find%20Box.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-543653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543780%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Find%20not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543780%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337239%22%20target%3D%22_blank%22%3E%40RichinMesa%3C%2FA%3E%26nbsp%3B%2C%20you%20find%20number%20formatted%20exactly%20the%20same%20way%20as%20in%20Format%20option.%20Number%20is%2050000%2C%20format%20as%20you%20define%20(number%2C%20or%20accounting%2C%20correct%20thousand%20separator%20and%20number%20of%20decimal%20points)%3C%2FP%3E%0A%3CP%3EWith%20this%20example%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20491px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112510i134BCA9BD233ACB4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFind%20ignores%20cell%20A3%20and%20selects%20C3%20(same%20value%20but%20different%20format).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-544008%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Find%20not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20your%20reply.%20It%20still%20doid%20not%20work.%20I%20tried%20what%20you%20said%20and%20added%20a%20%22.00%22%20to%20my%2050%2C000%20example.%20It%20matches%20the%20format%20exactly%20but%20does%20not%20find%20the%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20another%20sample%20by%20using%20a%20new%20worksheet%20and%20two%20values%20like%20you%20did.%20At%20first%20it%20did%20not%20work%20like%20my%20earlier%20attempt.%20Then%20I%20tried%20to%20find%2050%2C000%20without%20the%20%22.00%22%20and%20out%20of%20the%20blue%20it%20worked!%26nbsp%3B%20It%20also%20worked%20on%20my%20original%20worksheet.%20Maybe%20it%20was%20clicking%20the%20%22thousands%20separator%22%20in%20the%20find%20format%20that%20did%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-544175%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Find%20not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337239%22%20target%3D%22_blank%22%3E%40RichinMesa%3C%2FA%3E%26nbsp%3B%2C%20in%20Find%20what%3A%20bar%20it%20shall%20be%20the%20number%20without%20any%20formatting.%20Not%2050%2C000%20not%2050000.00%20Only%2050000.%20If%20you%20don't%20add%20Format%20option%2C%20it%20finds%20any%2050000%2C%20doesn't%20matter%20how%20it%20is%20formatted.%20If%20you%20add%20format%20options%2C%20it%20finds%20only%2050000%20formatted%20as%20you%20defined.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20try%20to%20find%2050%2C000%20or%2050000.00%20it%20finds%20nothing.%20More%20exactly%20no%20one%20number%20-%20Excel%20will%20be%20finding%20text%20%2250%2C000%22%2C%20not%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E
RichinMesa
New Contributor

I'm trying to find a value in a worksheet and Find is not working. If I search for text it works, but when I look for a value it doesn't find what I want. Here is what I enter: "Find what:" 50,000   "Within:" Sheet   "Search:" By Rows   "Look in:" Values "Format:" Number. No other options. (I also tried just the number and no comma, but it did not work either.) 

 

What is weird is there is a button or box that says "Preview*" that doesn't do anything and I can't get rid of it.  I pasted a screenshot of it. Any ideas what is wrong? 

 Excel Find Box.jpg

 

3 Replies

@RichinMesa , you find number formatted exactly the same way as in Format option. Number is 50000, format as you define (number, or accounting, correct thousand separator and number of decimal points)

With this example

image.png

Find ignores cell A3 and selects C3 (same value but different format).

@Sergei Baklan - thanks for your reply. It still doid not work. I tried what you said and added a ".00" to my 50,000 example. It matches the format exactly but does not find the value. 

 

I tried another sample by using a new worksheet and two values like you did. At first it did not work like my earlier attempt. Then I tried to find 50,000 without the ".00" and out of the blue it worked!  It also worked on my original worksheet. Maybe it was clicking the "thousands separator" in the find format that did it. 

Thanks for your help. 

@RichinMesa , in Find what: bar it shall be the number without any formatting. Not 50,000 not 50000.00 Only 50000. If you don't add Format option, it finds any 50000, doesn't matter how it is formatted. If you add format options, it finds only 50000 formatted as you defined.

 

If you try to find 50,000 or 50000.00 it finds nothing. More exactly no one number - Excel will be finding text "50,000", not number.

Related Conversations