Using XP Operating System to Search for Numbers Values in Excel Files

[Tim Childs]

Hi

I want to search for a numeric value (e.g. 283.57) in a large number of
Excel files stored in a directory.

While text seems to be stored as ASCII code and is visible in a file editor,
the numbers in Excel appear to be coded and certainly the XP file search
does not <do the business>.

Are there any ways round this? (and, out of interest, can anyone tell me how
the numbers are stored?)

Many thanks

Tim

PS I have done some searches on the archive but not turned up anything
relevant/helpful.

 

[Guest]

not sure what xp search is doing, but to find it, in excel:
file/open/tools/find will bring them up

 

[Pete_UK]

Well, I remember reading a post very similar to this some time ago, so
your searching wasn't very thorough.

Excel stores numbers in a special binary format defined by IEEE. Part
of the bit pattern is used to represent the mantissa (the binary
fractional part), and another part is used for the exponent (the power
of 2 that the number should be multiplied by). As such, you will not
be able to search for (decimal) numbers like 283.57, as they don't
exist in that format in the file (unless entered as text values).

Hope this helps.

Pete

 

[Tim Childs]

Hi

thanks for the response. However, I think that search will only look for
items within the FILE PROPERTIES (file last save date, Comments, Author etc)

I am looking for an item in a cell so it does not appear to work

Do you agree?

Thanks

Tim

 

[Tim Childs]

Hi

Thanks for response

Is it possible to find out the implied string by finding out the IEEE
conversion used and then search using that?

Tim

 

[carlo]

Hi

thanks for the response. However, I think that search will only look for
items within the FILE PROPERTIES (file last save date, Comments, Author etc)

I am looking for an item in a cell so it does not appear to work

Do you agree?

Thanks







- Show quoted text -

You could code the whole thing in VBS or VBA.

Connect to the directory,
loop: open every excel
output when he find the number
and exit the loop

Maybe a little bit too much effort if you only need to do it once.
But i think you will find most of the code parts by searching
in google.

hth

Carlo

 

[Pete_UK]

I don't know. Why do you want to use XP's Search tool anyway?

Pete

 

[Tim Childs]

Hi Carlo

Thansk for that. Will try that if necessary: am still hoping to find a less
onerous route (hopefully!)

Best wishes

Tim

 

[Tim Childs]

Hi

...because it works well and is very effective except for this specific area.
The functionality would help a lot on certain specific occasions.

best wishes

Tim