CODEWITHSUNDEEP
excelexcel-formula
Tariq Khalaf
Tariq Khalaf

Reputation: 89

find words in excel

you know when you click on control-F to find word and then when you click on find all it gives you a list of all the cells that have that word

how can we put that in a formula so that it would list those results in a sheet so say i have this sheet 1 

    A     |     B  
1 apple

in sheet 2

    A     |     B  
1 apple cider
2 peas
3 cucumber
4 apple
5 apple rum
6 carrots
7 beans
8 carrots and apples

i would like it the result to come out 

    A       |      B       |     C       |    D 
1 apple        apple cider   apple rum    carrots and apples

Upvotes: 1

Views: 116

Answers (4)

Tariq Khalaf
Tariq Khalaf

Reputation: 89

I've had to lookup the values manually .

Upvotes: 0

Marc
Marc

Reputation: 11633

Write this function in a module:

Public Function WordFinder(searchRange As Range, seekWord As String, iteration As Integer)
    Dim rangeCell As Range      'holder cell used in For-Each loop
    Dim rangeText As String     'holder for rangeCell's text
    Dim counter As Integer

    counter = 0
    'loop through cells in the range
    For Each rangeCell In searchRange.Cells
        rangeText = rangeCell.Value 'capture the current cell value
        'check if the seek word appears in the current cell
        If InStr(rangeText, seekWord) > 0 Then
            counter = counter + 1 'increment the occurrence counter
            If counter = iteration Then 'this is the occurrence we're looking for
                'return it
                WordFinder = rangeText
                Exit Function
            End If
        End If
    Next rangeCell
    WordFinder = "n/a" 'that occurrence number was not found

End Function

And across the top row of your results sheet, enter this formula in each cell:

=wordfinder(Sheet2!$A1:$A8,"apple",column())

column() increments with each column, so as you copy/paste it across the top row, it's counting up. The dollar signs make sure the reference remains absolutely on column A.

The 2nd parameter ("apple") can come from a cell, although I've hard-coded it here.

There might be a more elegant way to do this, but this is a quick & dirty way that should work.

Upvotes: 1

Emmanuel N
Emmanuel N

Reputation: 7449

To serach use Find()

    expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Example: 

     Cells.Find(What:="Cat", After:=ActiveCell, LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Upvotes: 0

Sylca
Sylca

Reputation: 2545

try to type this in formula bar: 

FIND(expression, text_value)

and also go through this link:

http://www.smartsheet.com/help-categories/formulas

Upvotes: 1

Related Questions