Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a word in a cell, but not when its a substring of another word in the cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a word in a cell, but not when its a substring of another word in the cell
On 4 Dec 2006 18:53:33 -0800, "Derrick Salmon" wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a word in a cell, but not when its a substring of another word in the cell
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/usin...l/blregexa.htm or Split(YourText," "), then serch the array. NickHK "Derrick Salmon" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make cell equal a word if a data validation cell has a word in it | Excel Discussion (Misc queries) | |||
How do a put a word on top of a word in a cell? | Excel Discussion (Misc queries) | |||
Macro for finding number of times a word repeats in a cell | Excel Programming | |||
Matching CELL text (full word) to full word in string in another | Excel Programming | |||
Add word in cell changes to word TRUE | Excel Discussion (Misc queries) |