Range contains specific text

Range contains specific text

=COUNTIF(rng,"*"&value&"*")>0

If you need to determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function with wildcards.

In the example shown, the formula in D5 is:

=COUNTIF(rng,"*"&D5&"*")>0

How this formula works

The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero.

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the  value in D5, the formula will count the substring if it appears anywhere in any cell of the range.

Any positive number means the value was found, so you can use the greater than operator (>) to run a simple test on the result of COUNTIF to force a TRUE or FALSE result.

You can also wrap the formula inside an IF statement to force a specific result. For example, to return "Yes" or "No",  use:

=IF(COUNTIF(range,"*"&value&"*"),"Yes","No")

Range contains one of many values

=SUMPRODUCT(--(rng=values))>0

If you need to determine if one or several values exists in a column or range of cells, you can use a formula based on the SUMPRODUCT function.

In the example, the formula in cell D5 is:

=SUMPRODUCT(--(rng=B5:D5))>0

How the formula works

Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.

The double hyphen (technically called a double unary) will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array. Logically, any result greater than zero means that the formula found at least of the values in rng. So, checking the result to see if it's greater than zero causes the formula to return TRUE if any value was found and FALSE if not.

With hard-coded values

You can also hard code the search values into the formula, using what is known as an "array constant".  For example, if you want to look for 3 values: red, cyan, and magenta inside the range H2:H8, you can use:

=SUMPRODUCT(--(H2:H8={"red","cyan","magenta"}))>0

In the above example {"red","cyan","magenta"} is the array constant, which is one way to supply multiple values in a single argument.

Excel TEXT Function

Purpose: 

Convert a number to text in a number format

Return value: 

A number as text in the given format.

Syntax: 

=TEXT (value, format_text)

Extract date from a date and time

=INT(date)

Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion and .5 is the time portion.

If you have dates with time values and you want to extract only the date portion, you can use a formula that uses the INT function. So, assuming A1 contains the date and time, June 1, 2000 12:00 PM, the formula below returns just the date portion (36678):

=INT(A1)

The time portion of the value (the fractional part) is discarded. You could also use the TRUNC function with the same result:

=TRUNC(A1)

Get age from birthday

=INT(YEARFRAC(birthdate,TODAY(),1))

If you need to calculate a person's age from their birth date, you can do so with the YEARFRAC, INT, and TODAY functions. In the generic version of the formula above, birthdate is the person's birthday with year, and TODAY supplies the date on which to calculate age. Because this formula uses the TODAY function, it will continue to calculate the correct age in the future as well.

In the example, the active cell contains this formula:

=INT(YEARFRAC(D4,TODAY(),1))
Here's how the formula works:

YEARFRAC calculates a decimal number representing the fraction of a year between two dates. To work out the fraction of a year as a decimal value, Excel uses whole days between two dates. This is straightforward in Excel, because all dates are simply serial numbers.

So, in this case, birthdate is supplied as the start_date from cell D4, and today's date is supplied as the end_date, courtesy of the TODAY function.

The result of this YEARFRAC for Michael Chang, here in April 2013, is something like this:

12.9458243692548

Then, the INT function takes over and rounds down that number to the integer value, which is the number 12. Even though Michael is close to being 13 years old, he's still just 12 by the books, so this is the correct result.

If you want to calculate a person's age as of a given date, just replace the TODAY function with that date, or a cell reference to that date.

Get day name from date

TEXT(B4,"dddd")

If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs.

Do you just want to display the day name?

If you only want to display a day name, you don't need a formula – you can use a custom number format. Select the date, then go to Format cells (Ctrl + 1 or Cmd + 1)  > Custom, and enter one of these custom formats:

"ddd" // "Wed"

"dddd" // "Wednesday"

Excel will display only the day name, but it will leave the date value intact.

Do you want to convert the date into a day name?

If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like "ddd". The formula looks like this:

=TEXT(B4,"ddd")

The TEXT function converts values to text using the number format that you provide. Note that date is lost in the conversion, only the text for the day name remains.

A more flexible way to get a day name from a date

If you don't want to use a helper table, you can also map weekday number to a month name directly in the CHOOSE function like so:

=CHOOSE(WEEKDAY(B4),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

In this case, enter the weekday names you want to return (abbreviated or not) as values in CHOOSE, after the first argument. WEEKDAY will extract a weekday number, and CHOOSE  will use this number to return the nth value in the list. This works because WEEKDAY returns a number 1-7 that corresponds to a given day of the week. With default settings, Sunday = 1 and Saturday = 7. See WEEKDAY for more info.

CHOOSE is more work to set up, but it is also more flexible, since it allows you to map a date toany values you want (i.e. you can use values that are custom, abbreviated, not abbreviated, different language, etc.)