Finding a word in a cell, but not when its a substring of another word in the cell

[Derrick Salmon]

Hi All

I have cells in an excel spreadsheet with contents (employee short-form
initials) like [tv pm dsab sa] - this is the content of a single cell.
When such cells are scanned for contents equal to, say, 'ds' or 'sa' I
don't want to flag on 'dsab' (which contains both 'ds' and 'sa'), but
only on matches to 'ds' or to 'sa' as unique values.

I've tried using Find with what:="xx " i.e. using the space between
words as part of the search string, bu this fails on the last entry in
the cell (above, [ ... sa] has no trailing space in the cell content so
is not found

There has to be a better (real) way ...


cheers


Derrick

 

[Ron Rosenfeld]

Hi All

I have cells in an excel spreadsheet with contents (employee short-form
initials) like [tv pm dsab sa] - this is the content of a single cell.
When such cells are scanned for contents equal to, say, 'ds' or 'sa' I
don't want to flag on 'dsab' (which contains both 'ds' and 'sa'), but
only on matches to 'ds' or to 'sa' as unique values.

I've tried using Find with what:="xx " i.e. using the space between
words as part of the search string, bu this fails on the last entry in
the cell (above, [ ... sa] has no trailing space in the cell content so
is not found

There has to be a better (real) way ...


cheers


Derrick

You can do it easily enough with Regular Expressions. You can use a word
boundary token to ensure you only look at strings that are NOT embedded in
other strings.

But I don't know what you mean by "flag on".

You can implement Regular Expressions either by using VBScript in VBA, or by
downloading and installing Longre's free morefunc.xll add-in from
http://xcell05.free.fr

If you do the latter, the formula:

=REGEX.FIND(A1,"\bsa\b") 0r
=REGEX.FIND(A1,"\b"&B1&"\b") where B1 contains the string to find.

would find the location of the "sa" at the end of your test string. And it
returns a zero if the string is not found, rather than an error.

In your test string: sa-->12; ds-->0; tv-->1

You can also use Longre's functions in VBA.


--ron

 

[NickHK]

Derrick,
The Find method has an argument LookAt, which according to help:
"LookAt Optional Variant. Can be one of the following XlLookAt constants:
xlWhole or xlPart"
Does that work for you ?

Otherwise, maybe write you own FindEx method to give you more control.
Regular Expressions (RegEx)
http://visualbasic.about.com/od/usingvbnet/l/blregexa.htm

or Split(YourText," "), then serch the array.

NickHK