What is IS function in Excel? IS functions, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT are under the Information functions in Microsoft Excel. Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference…
Tag: ISNUMBER function
How to test a range for numbers in Excel
To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a…
Split numbers from units of measure in Excel
To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text. Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if…
How to check cell that contains specific text in Excel
To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. Formula =ISNUMBER(SEARCH(substring,text)) Explanation In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if the substring is found, and…
How to check cell that contains one of many with exclusions in Excel
To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the formula in C5 is: =(SUMPRODUCT(–ISNUMBER(SEARCH(include,B5)))>0)…
How to check if cell contains number in Excel
To test if a cell (or any text string) contains a number, you can use the FIND function together with the COUNT function. In the generic form of the formula (above), A1 represents the cell you are testing. The numbers to be checked (numbers between 0-9) are supplied as an array. Formula =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0 Explanation In the example the formula in…
How to check if cell contains one of many things in Excel
This tutorial shows how to check if cell contains one of many things in Excel using example below: If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 Explanation Context Let’s say you…
How to check if cell contains all of many things in Excel
If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions. Case study: Let’s say you have a list of text strings in the range B5:B8, and you want to find out if these…
How to abbreviate names or words in Excel
To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms. Only capital letters will survive this formula, so the source text must include capitalized words. You can use the PROPER function to capitalize words…
How to check cell contains which things in Excel
If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function. Setup Suppose you have a cells that contain text that mentions various colors, and you want to figure out which colors are…
How to count keywords in a range of cell
To count the number of specific words or keywords that appear in a given cell, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(keywords,A1))) Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords. Explanation…
Excel Data validation require unique number
Set criteria to accept only unique number, ie no duplicate. To allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions. Formula =AND(ISNUMBER(A1),COUNTIF(range,A1)<2) Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is…
Excel Data validation must not contain
Set criteria to reject specific data in Excel To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(list,A1)))=0 Explanation In the example shown above, the data validation applied to B5:B11 is: =SUMPRODUCT(–ISNUMBER(SEARCH(list,B5)))=0 Note: Cell references in data validation formulas are relative to the upper left cell in…
Excel Data validation must contain specific text
How to set criteria in Excel to accept specific text. To allow only values that contain a specific text string, you can use data validation with a custom formula based on the FIND and ISNUMBER functions. Formula =ISNUMBER(FIND(“txt”,A1)) Explanation In the example shown, the data validation applied to C5:C9 is: =ISNUMBER(FIND(“XST”,C5)) Note: Data validation rules are triggered when a user…
Highlight numbers that include symbols in Excel
This tutorial shows how to Highlight numbers that include symbols in Excel using the example below; Formula =IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)=”<“,(MID(B4,2,LEN(B4))+0)<input)) Explanation To highlight numbers less than a certain value, including numbers entered as text like “<9”, “<10”, etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a number. In the example shown,…
Excel Data validation allow numbers only
This tutorial shows how to create Data validation to allow numbers only in Excel using the example below; Formula =ISNUMBER(A1) Explanation Note: Excel has several built-in data validation rules for numbers. This page explains how to create a your own validation rule based on a custom formula. To allow only numbers in a cell, you can use data validation with a custom…
Highlight cells that contain one of many in Excel
This tutorial shows how to Highlight cells that contain one of many in Excel using the example below; Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 Explanation To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied to B4:B11 is based on…
Highlight cells that contain in Excel
This tutorial shows how to Highlight cells that contain in Excel using the example below; Formula =ISNUMBER(SEARCH(substring,A1)) Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your own formula, as explained in this article. If you want to…
Categorize text with keywords in Excel
This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named range E5:E14, and “categories” is the…
IF with wildcards in Excel
This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, the IF function does not…
If cell contains in Excel
This tutorial shows how to calculate If cell contains in Excel using the example below; If you want to copy cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. Formula =IF(ISNUMBER(SEARCH(“abc”,A1)),A1,””) Explanation Once you find a value you’re looking for you can copy it to another location,…
Match first occurrence does not contain
This tutorial shows how to Match first occurrence does not contain in Excel using the example below; Formula {=MATCH(FALSE,logical_test,0)} Explanation To get the position of the first match that does not contain a specific value, you can use an array formula based on the MATCH, SEARCH, and ISNUMBER functions. In the example shown, the formula in E5 is: {=MATCH(FALSE,data=”red”,0)} where “data” is the named…
Lookup last file version in Excel
This tutorial shows how to Lookup last file version in Excel using the example below; Formula =LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range) Explanation To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is: =LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files) where “files” is the named range B5:B11. Context…
Index and match on multiple columns in Excel
This tutorial shows how to calculate Index and match on multiple columns in Excel using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where “names” is the named range C4:E7, and…
Get first match cell contains in Excel
This tutorial shows how to Get first match cell contains in Excel using the example below; Formula {=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))} Explanation To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match. This is an array formula and…
Extract all partial matches in Excel
This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is: =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) with the following named…
Average the last 3 numeric values in Excel
This tutorial shows how to work Average the last 3 numeric values in Excel using the example below; Formula {=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))} Explanation To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example shown, the formula in D6…
Count cells that do not contain many strings in Excel
This tutorial shows how to Count cells that do not contain many strings in Excel using the example below; Formula {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} Explanation To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is: {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} where “data” is the named…
Count cells that contain numbers in Excel
This tutorial shows how to Count cells that contain numbers in Excel using the example below; Formula =COUNT(range) Explanation To count the number of cells that are not blank, use the COUNT function. In the example, the active cell contains this formula: =COUNT(B4:B8) How this formula works The COUNT function is fully automatic. It counts the number of cells in the…
Count cells that contain specific text in Excel
This tutorial shows how to Count cells that contain specific text in Excel using the example below; Formula =COUNTIF(range,”*text*”) Explanation To count the number of cells that contain certain text, you can use the COUNTIF function. In the example above “*” is a wildcard matching any number of characters. In the example, the active cell contains this formula: =COUNTIF(B4:B11,”*a*”) How this formula works COUNTIF counts…
Count cells that contain either x or y in Excel
This tutorial shows how to Count cells that contain either x or y in Excel using the example below; Formula =SUMPRODUCT(–((ISNUMBER(FIND(“abc”,range)) + ISNUMBER(FIND(“def”,range)))>0)) Explanation To count cells that contain either one value or another, you an either use a helper column then tally up the count, or a more complex single cell formula. Background When you count cells with “OR”…
Count rows with multiple OR criteria in Excel
This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(–((criteria1)+(criteria2)>0)) Explanation To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is: =SUMPRODUCT(–((C5:C11=”blue”)+(D5:D11=”dog”)>0)) How this formula works In the example shown, we want to count rows where…
Count rows that contain specific values in Excel
This tutorial shows how to Count rows that contain specific values in Excel using the example below; Formula =SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)))>0)) Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: {=SUM(–(MMULT(–(data=90),TRANSPOSE(COLUMN(data)))>0))} where data is the named range B4:B12. Note: this is an array formula and must be…
SUMPRODUCT count multiple OR criteria in Excel
This tutorial shows how to work SUMPRODUCT count multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(ISNUMBER(MATCH(range1,{“A”,”B”},0))*ISNUMBER(MATCH(range2,{“X”,”Y”,”Z”},0))) Explanation To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is: =SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{“A”,”B”},0))* ISNUMBER(MATCH(C5:C11,{“X”,”Y”,”Z”},0))) This formula returns a count of rows where column one is A…
Sum if cells contain either x or y in Excel
This tutorial shows how to Sum if cells contain either x or y in Excel using the example below; Formula =SUMPRODUCT(–((ISNUMBER(SEARCH(“cat”,range1)) + ISNUMBER(SEARCH(“rat”,range1)))>0),range2) Explanation To sum if cells contain either one text string or another (i.e. contain “cat” or “rat”) you can use the SUMPRODUCT function. Background When you sum cells with “OR” criteria, you need to be careful not…
Sum if one criteria multiple columns in Excel
This tutorial shows how to Sum if one criteria multiple columns in Excel using the example below; Formula =SUMPRODUCT((criteria_range=”red”)*(sum_range)) Explanation To sum multiple columns conditionally, using one criteria, you can use a formula based on the SUMPRODUCT function. In the example show, the formula in H5 is: =SUMPRODUCT((B5:B10=”red”)*(C5:E10)) How this formula works This first expression in SUMPRODUCT is the criteria,…
How to Check If A Cell Contains Specific Text in Excel
To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. The ISNUMBER functions is used to check for a numeric value and the SEARCH function returns the location of a substring in a string. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text string, use the SEARCH function.…