Optimizing a string search function?

**HorsePop** · 05-15-2013, 08:06 AM

Hello Excel Forum.

I'm still doing stuff with product lists.

Latest thing is I've written a function that when passed two cell refs and a number of characters searches through the second cell for snippets of the length specified and then returns an integer for how many matches it was able to make.

The idea is you use it to match "Apple" against "Red Granny Smith Apples From Spain" or "Apples from Spain" or "Green Apples" etc.

It's a bit of an analogue tool but it can be handy.

My question is can I make this run more efficiently?

It's not terrible but I'm wondering if I could make it do this quicker.

Please Login or Register  to view this content.

I also feel like there's probably a much more elegant way to do this.

**Norie** · 05-15-2013, 08:47 AM

How do you use this function? In particular what value is number_of_chars?

If you want to count how many times a word appears in a longer string you can use this, where A1 contains the string you want to search and B1 the term to search for.

Formula:

Please Login or Register  to view this content.

**HorsePop** · 05-15-2013, 09:16 AM

Depending on the sheet I am using it on I might only want it to match strings of a certain length.

Cell 1 may contain several things I need to search Cell 2 for.

The string in either cell isn't necessarily formatted (yeah, the supplier annoys me a lot) with anything other than spaces in between words therefore it is handy to be able to specify I do want it matching strings this long but not strings any shorter.

e.g.

Cell A contains 1.2kg, 1.4kg, 1.6kg, 1.8kg
Cell B contains Blah blah blah 1.4 kg blah blah 1. blah 4k

I do want it to count the 1.4 but not the 1. or the 4k so I specify a number_of_chars of 3.

I suppose I could use column-to-string to explode the string out but it gets very messy as the cell may contain a whole sentence.

The same Cell A may appear next to several Cell B's or vica versa so I consider it beneficial to be able to look through each chunk of text in there.

**Norie** · 05-15-2013, 09:19 AM

What exactly are you trying to do?

If you want to find if a substring is within another string you can use SEARCH.

You don't need to specify length and it will only work for exact matchs.

**HorsePop** · 05-15-2013, 09:51 AM

I've got two untidy strings which may or may contain bits of useful data.

I want to know if any bit of useful data in one string is present in another string.

I know roughly what format some of the useful data is going to be in. Number dot number for example.
The problem I've got is where it's a string. The only sensible route there seems to be to limit it by size.
I want the option because I don't always want it to compare two character strings. If it's like "1.4" and going to be that in both strings then I'd rather search for three character strings at a time. If it's a name like "Apple" "Orange" "Pear" "Satsuma" then searching for the minimum length name. I might want to use longer chunks for other data.

**Norie** · 05-15-2013, 09:58 AM

Sorry, you've lost me.

Why would searching for only part of a string make sense?

What if you had a string like this?

'everything apart from pears'.

If you were checking for 'apple' but only looked for 'ap then you would find it in 'apart', so the result would be wrong.

**HorsePop** · 05-15-2013, 10:26 AM

Oh yeah, ignore the bit about two chars. I've been using it for something today where the minimum bit of data I was searching for was two chars. :$

"Everything apart from" is a problem but at least by flagging that line I can then manually ignore it.