Comparing a string to a text file

Hey folks,

I have a series of strings for which I want to test each one individually and find out whether it appears within a larger text.

Now, for a normal text I would just do (and have done) something akin to:
=SEARCH(C1,'Text to Test'!$A$1)
and make a simple column out of it.

But what do I do if the text is too long to fit within a cell (i.e., excel gives me an error beep when I try to paste it)? Is there any way to have Excel compare the string in C1 to the text of a Word or .txt file?


Thanks,
Daniel

Originally Posted by KiPA

You're sure that the length of the text is the matter here? Excel 2003 cell can take as much as 32767 characters. That's about 8 pages with default font with my Word 2003.

Yup. I was doing fine earlier with shorter texts but I need to search for strings within documents of up to hundred pages.

Originally Posted by KiPA

Ok this might not be ethical since I'm using value formula for a text. It works if you have like "hi this is me" and you are looking for 'this'. If your source is "hithisisme" it wont work.

Import a txt file as xls file or just open the file and save as xls document. You can look for a 'value' with something like this:

=IF(ISERROR(VLOOKUP(C1;test.xls!$A$1:$A$1000;1;FALSE))=TRUE;0;1)

Of course the formula returns 0 if the text ('value') does not exist and 1 if it does.

Edit: nah, never mind. It's too conditional :|

Not sure if this is what you meant about "too conditional", but it gives me an error merely when trying to enter the formula, highlighting C1.

Originally Posted by KiPA

Yeah the error most likely comes because of ";". Replace all of them with ",". With too conditional I meant that you can practically have only one word in each column if this is to work.

Yeah, I see that in the results. It needs to be the exact text of one of the target cells; it doesn't search them for substrings. Which is one reason I was using =SEARCH. But it's annoying that =SEARCH doesn't let me take a range of cells.

Originally Posted by KiPA

Oh you are willing to split the text, no problem (I hope).

In that case you could e.g. use a formula like this:

=OR(ISERROR(SEARCH(C1;E1;1))=FALSE;ISERROR(SEARCH(C1;E2;1))=FALSE)

C1 = text to search
E1 and E2 = splitted text where you search from

This applies of course if you can fit your text into two cells. I'm sure you can expand this if needed. Also this returns TRUE instead of number, if the text was found.

What if the text is extremely long and may take up to several dozen cells even when split? I had occasionally needed to use a formula similar to the above but now the files are longer and longer and even merely splitting them manually could take quite some time.

Is there really no way to get it to search an external Word file, or an embedded Word file, or something like that?