No announcement yet.

search a cell for a specific word

  • Filter
  • Time
  • Show
Clear All
new posts

  • search a cell for a specific word

    Hello, How do you search a cell for a specific word. For example,

    cell A1 contains the following: 114 third ave Cedar Rapids Texas

    I want to write a function that would search the string for the word "Cedar" and then return it in C1. The word can be in any position of the cell and not all cells in column A have the word in that case i would want to return NA.

  • #2
    Hi SEB,

    Try this function. Use FIND is it needs to be case sensitive.




    • #3
      Thanks that works great. Just to expand on this, say for instance i had multiple words to search for and the result returned would be different in every case. For example, based on the above. How would i modify the above to search for cedar, oak, elm and maple? Would i need multiple columns or could i do it in one column?


      • #4

        Hi SEB,

        It helps if you post a simple example of what you are after but from what you have said so far....

        try setting up a cell in which you can type a search eg. A1,,, goto insert/name and call it something like "searchcell". then use the formula that Andy gave you except replace the first instance of "cedar" with "*" & search & "*" this will search the queried cell for whatever text is entered within the searchcell for example if you enter "a" it will return a true (therefore not an error and the output will be CEDAR).

        Assuming you have a spread sheet full of addresses and other information you can set this combination of IF and SEARCH functions to return a row number if the text entered in the search cell is found.

        eg if you have titles for your spreadsheet columns in row3 and set a search cell to be A1 enter the following in g4
        if(or(iserror(search("*" & searchcell & "*", a3)),searchcell=""), "", row())

        this is the first stage in a rather beautiful search function (unfortunately not designed by me but I have forgotten where it came from... I will have to start keeping an ideas database so I can appropriately reference the genii that come up with this stuff).

        Ok you have a search now that will output a row number in the G column if the text that is entered in A1 is found. In the next column (H) you can enter a formula such as


        assuming you have four columns (A to D, used in the SMALL function) this formula will look down the G column and find the smallest number (the first encountered row number) and then output from the columns specified (A to D) the cell determined by the row number (the smallest in G, replace the #n in the formula with last row number in your list). The I4 reference (in SMALL($G$4:$G$#, I4) is numbered from 1 (I4) to the last row in your spreadsheet (I#)... so in H4, the I4 reference will be 1, in H5 it will be 2 etc... so H4 will look for the first smallest row number, H5 the second smallest and so on.

        so if the search text entered in the searchcell is found INDEX(A:D, small(g4:g10),1) will return the value of the cell row4column1 from the columns outlined (in this case A4).

        Have a look at the attached example, no VBA required. Sorry if this is not what you are after.
        Attached Files