Search inside excel cells?

[Cynthia Moore]

Can XY search for a string inside an Excel worksheet cell?

I need to make a change to the syntax of a subroutine I wrote some time ago. The change will cause any calls using the old syntax to break. I tried using Windows search with "*.xls*" in the file name field and "=xyz(" in the text field. It didn't find any matches.

[highend]

Afaik no.

Searching for =xyz(

wouldn't work anyway. Internally (.xlsx files are nothing more than .zip files with (mainly) .xml files inside) functions
are stored in .xml syntax.

So in your case:
<f>XYZ(...)</f>

FileLocatorPro is able to find these files but only after it's settings for Office/PDF documents is changed from (x) Text Search to (x) Deep Search

Ofc the correct iFilters must be installed as well...

[PeterH]

Just a thought:
XY does use iFilter to view the contents of, for example, an .xlsx file.
Couldn't it also use iFilter to search for contents?

Sure: this would be an enhancement request.

As I see it it would be slower than the current search - so it could make sense to explicitely select this as a kind of Deep Search.
(But else searching .xlsx etc. seems to make no sense at all...)

[TheQwerty]

In a quick test here Microsoft's IFilter only returns the displayed values, not a cell's formula, and attempting to just find strings does not work because of encoding/compression.

Without resorting to other tools, a possible (and ugly) workaround is to rename the .xlsx files to have an extension of .zip and then perform a contents search (of type Text) for the formula. The Zip IFilter should return the contents of the various files within the .xlsx, which might include your desired formula. Unfortunately, that won't help with the old non-zip Office formats.

2016-01-06 072902.png (17.13 KiB) Viewed 3635 times

[PeterH]

OK: I just had thought about some *displayed* text of the sheet, for example (part of) text of a cell only containing a string. Shure: could also be the result of some calculation. This might be easy enough - and could be very helpful.

I do understand that it's levels deeper to deal with a formula, or other info from the source of the sheet.
(So sorry I ignored the basic wish of this thread dealing with the formula. )

By the way: just copied an .xlsx, renamed to .zip, and extracted: quite interesting. But too lazy to continue this

[TheQwerty]

I do understand that it's levels deeper to deal with a formula, or other info from the source of the sheet.
(So sorry I ignored the basic wish of this thread dealing with the formula. )

No need to be sorry! I had the same thought at first - it wasn't until I tried it that I found the IFilter returns so little of a spreadsheet's content.

Quick tip: To see what the registered IFilter for the current file returns, which is also what XY's text content search uses, run this script:

Code: Select all

text extracttext();

By the way: just copied an .xlsx, renamed to .zip, and extracted: quite interesting. But too lazy to continue this

Yep... the insides of the Office Open XML files are eye-opening. They also very eloquently illustrate one of my biggest complaints with Microsoft designs - they encompass mind-numbing possibilities to meet the desires of just about any customer, but in doing so make things frustratingly difficult for the tasks performed by the majority.

Just look at any Office application; they are extremely powerful and overflowing with features, but it's hard to imagine anyone using 50% of its capabilities and most probably don't get above 10%.

[PeterH]

Strange!

text extracttext(); works for .txt, .doc, .xls.

But: for .xlsx and .docx it shows "Failed to load an IFilter. Error 80040154."
(and: for .pdf it shows "Failed to load an IFilter. Error 80004005.")

(Installled is office 2010, preview is OK for all of these files.)

[highend]

Code: Select all

text extracttext();

works fine for me on "Mapp1.xlsx" with 2 values in A1, B1 and the sum of it in C1 (Win 7 x64 SP1 German, Office 2010 Pro Plus x86, correct iFilters...)

100120013002

(and: for .pdf it shows "Failed to load an IFilter. Error 80004005.")

But this has nothing to do with Office, that's the iFilter of you pdf viewing application?

[PeterH]

1) for .xlsx, just tested with your mini-file: same error shown.
(But still: XY-preview is OK!)

2) sure .pdf wasn't office
Just had noticed it - and so used the smaller font to "separate"
It's PDFXViewer, V2.5.315 (seems to be the current Viewer - *not* Editor)
...and just had to notice: no XY preview for pdf. Did I miss something?
(DoubleClick correctly opens in PDFXViewer)

[highend]

When I install PDF-Xchange Viewer v2.5 Build 315.0 (which seems to install the x86 filters on a 64-bit OS automatically)
I have a working pdf preview in the latest XYplorer beta and extracttext() works fine on any selected .pdf as well.

Conclusion -> Your iFilters installation seems to be broken (at least it's not working as it should)...

Try to uninstall PDF-Xchange Viewer, restart your system, install it again, does at least the preview in XY works after that procedure?

[PeterH]

Could make sense...

So I will plan for re-install in the next days.

Thanks a lot
and good night!
(Sleeping avatar missing, so misuse: )

[TheQwerty]

Keep in mind that the IFilters and the preview handler are entirely different things.

If memory serves when I was using Office 2010 on Windows 7 I wasn't able to ever get the IFilters working in XY - Microsoft's installer would only install those matching the OS bitness and my attempts to register the 32-bit ones manually never met success.

Since then I had to have IT re-image my machine, which upgraded me to Office 2013. Today's the first I noticed that it seems to have improved the IFilter situation, but I didn't really do a comprehensive test of that this morning.

[highend]

Enternal's post (http://www.xyplorer.com/xyfc/viewtopic.php?f=3&t=13091)

contains the x86 iFilters that work at least on my installation (x64) here. Just follow the included
README.txt to install them (and before you overwrite existing files, make a backup of them first just to be on the safe side)...

[PeterH]

Ouuuh - seems that's it (with my PC)

Re-install of PDFXViewer *hung* at the very end - could only be terminated by task mgr.
Now it's just working (good or bad) exactly as before.

In the last time I experienced different "little problems" with this system - I think the installation just become "a bit" (or more) buggy and used-up.
(Built the PC and installed the system more than 6 years ago. Many things have happened since then )

So either I will wait for some new hardware and build a new PC (planned to do it this in the next months) or re-install the complete system. (Maybe by dual-boot so I can use the old system untill the new is ready to go, and maybe win10? 10 was planned for the new PC.)

So now I just have a reason more to think about it. But in the moment it still does what I need - so there's a bit time left to decide

And OK: I shouldn't think about XY problems untill I've cleaned up.
Thanks for your help and ideas

[highend]

If you don't care about privacy? Go for Win 10

You've made sure you have drivers available for old / exotic hardware? When TH2 was released it forgot about my Dell's Notebook WLAN driver (which was working flawlessly before) so I couldn't use it anymore (there are no external Win 10 drivers for this Notebook (4 years old))...

Mh, that's off topic here