Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: search a cell for a specific word

  1. #1
    Join Date
    17th August 2004
    Registered Users

    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.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    7th March 2003
    Essex, England
    Super Moderators
    Hi SEB,

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



  3. #3
    Join Date
    17th August 2004
    Registered Users
    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?

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    6th September 2004
    Registered Users
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Search For Files Containing Specific Text In Cell
    By lifeson99 in forum Excel General
    Replies: 5
    Last Post: January 5th, 2009, 09:30
  2. Replies: 7
    Last Post: May 6th, 2008, 18:15
  3. Color Row If Cell In Column Contains Specific Word
    By shullmf in forum Excel General
    Replies: 3
    Last Post: April 17th, 2008, 03:05
  4. Replies: 11
    Last Post: May 31st, 2007, 03:55
  5. Extract Specific Word From Cell
    By Student118 in forum Excel General
    Replies: 1
    Last Post: November 2nd, 2006, 13:37


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts