Excel formula to check to see if a cell contains a string

The SEARCH formula used in conjunction with the ISNUMBER formula in Excel will allow you to search for a particular string inside the contents of a cell. So in this example the formula:

=IF(ISNUMBER(SEARCH("whale",A2)),A2,B1)

. . . will search the contents of cell A2 for the string 'whale'. If a result is found, the formula will pull the data from cell A2. If it is not found the result from cell above will be entered. When the formula is pulled down the column will be populated only when a hit is found.

Using this formula has an advantage over simply using the contains option in a filter on the column. If you have raw text for multiple documents, and you want to pull the date for each or the title for each, and there is a unique identifier for those values you can search for (and a break indicating where documents begin and end), you can create an index that will have the searched for value in a column. As shown in this example the date is then directly associated with the Bates number for different documents.

Tracking the time of testimony with Excel

Tonight's tip shows how you can use a simple SUMIF formula in Excel to automatically track how much time each party has used during a trial.

As shown in this example, the SUMIF formula first references a range in which the parties names are listed (in this example in column C) with an absolute reference . The party's name is listed to the left of the formula in cell B12. The last part of the SUMIF formula is a column listing the amount of time between the beginning and end of one side's questioning. The formula only adds up the values in column G which have the value in B12 in column C.

=SUMIF($C$2:$C$9,B12,$G$2:$G$9)

Excel Formula to Find Date in Multiple Date Ranges

You can use an Excel formula to check where multiple dates listed in one column, are found between a beginning and ending date range in a separate table, and pull the associated value from a third column that lines up with the date range. See this example:

=LOOKUP(2,1/(G:G<=A2)/(H:H>=A2),F:F)

In the formula, the date you want to search for is in A2. This date must fall between the date range which begins in column G and ends in column H. The value to be associated with the date range is given in column F. So when we search for 7/1/2020, we find that it falls between 4/5/2020 and 9/22/2020, which is the date range during which Model D was manufactured.