Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to filter entries containing short forms of words (for example "Rd" for "Road"), however the filter function for containing "Rd" would return me all entries with words containing "Rd" like forward, card etc.

Is there a way to filter entries with just the word "Rd" itself standing alone in the sentence and not being a part of other irrelevant words?

share|improve this question
    
Can you filter for " Rd " with a leading and ending space? –  nixda Nov 7 '13 at 11:22
    
Maybe only the leading one: " Rd" –  douglaslps Nov 7 '13 at 11:29
    
What do you mean by leading and ending space? –  user2964366 Nov 7 '13 at 11:29
    
But that only applies to the Ctrl+F function doesn't it? That would only point to me entries containing that but it doesn't filter results for me. Is there anything I can do with the Autofilter? –  user2964366 Nov 7 '13 at 11:32
    
@user2964366 Of course. In your autofilter dialog, select contains and type ` Rd ` (notice the space before and after). This will filter all sentences where Rd stands alone. Note that this won't find Rd at the start or beginning of your sentence since the second space is missing there. –  nixda Nov 7 '13 at 12:01

2 Answers 2

This is a small adjustment to CLockeWork's solution. If the helper column contined:

=--ISNUMBER(FIND(" Rd"," " & A1 & " "))

This will return 1 if the cell contains:

<>Rd<>

or begins with:

Rd<>

or ends with:

<>Rd

where <> represents the "space" character.

share|improve this answer
    
and so those are the entries that I do not want? –  user2964366 Nov 7 '13 at 11:50
    
Either way....if you set the filter option on the helper column to 1 , then you will get all the Rd s. Just try it out on some scrap data. –  Gary's Student Nov 7 '13 at 11:54

Simplest solution: build an extra column with a formula that looks for short form words (without knowing more about your data I can't help specifically with the formula)

Then just filter on that column.


So if I knew I wanted to filter 2 letter words I could create a column with the formula:

=LEN($A2)=2

Or if using a table:

=LEN([@[TargetColumn])=2

Either of these would return a TRUE or FALSE value that you could then filter on.

share|improve this answer
    
It looks like the entry will contain that words "Rd" but not limit to those - like Abbey Rd. Therefore I don't think you can filter by the lenght. –  douglaslps Nov 7 '13 at 11:20
    
This would work, but I think the OP has other words in the cells, like main rd –  Raystafarian Nov 7 '13 at 11:20
    
Yes, my excel entries are all sentences so I'm looking for entries that contain "Rd" as one of the words in the sentence but "Rd" as an independent serving as a shortform. For instance, "Accident at Abbey Rd" and "he is walking towards me": both will be given to me if I filter using the "containing" filter function. But I only want the first one. –  user2964366 Nov 7 '13 at 11:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.