Finding exact words i strings

**Flabbergaster** · 11-14-2012, 03:45 AM

Hi

I have a column B with a lot of text strings (B2:B124508), and a row1 with approx. 1500 words (C1:BFF1) I'd like to look for in these texts.

Trying to autofill the formula = if(iserror(search(C$1;$B2));1;0) , the program crashes or at least kindly announces that it can't fulfill the task with the available resources, blah blah....

Is it just the way it is? That is, I'm expecting more than my computer and or the program can handle? Or are there any kind genius VBA way to work around this problem?

**JBeaucaire** · 11-14-2012, 12:07 PM

That formula is pretty simple. What happens when you add it to just a 100 rows at a time or so?

I'm asking because those functions are not volatile, they should only need to calc once per cell and then go dormant unless you updated the row or column text strings again. So the trick is to just get them in there one time.

**Flabbergaster** · 11-16-2012, 04:36 AM

It works fine if I do it for a 100, maybe 1000 rows at a time. But beyond that it crashes. Maybe my database has grown to big for Excel..?

**JBeaucaire** · 11-16-2012, 01:48 PM

Maybe my database has grown to big for Excel..?

First thing to teach yourself when using Excel.... "Excel is not a database".

Second thing: "Everyone uses Excel like a database."

Third thing: "Eventually people doing #2 are faced with the reality of #1."

If you're doing that much business and have that much important data that must be crunched from a single place, then you MUST bite the bullet and move to an actual database like Access or SQL, whatever.

Whatever the frustration in time and cost to make this transition, spend it, now, make yourself. 6 months from now you will look back and say, "What was I thinking, this is so much better!".

And never forget the first lesson.