Excel 2010: Categorizing Numeric & Text Data
Excel 2010 provides a list of functions that lets you categorize the value types in the spreadsheet. You can use a wide range of built-in function or create custom formulas to categorize your data set into different group to perform further complex operations. Using ISTEXT, ISNUMBER and ISNONTEXT functions, you can easily make categories of data types (text or numeric), and perform more operations over two logical values TRUE or FALSE, as these functions yield TRUE or FALSE against the specified values. These functions take a single argument, which can either be the location of cell where data is residing or direct values. The above mentioned function help users identify the type of data from a large data set. For instance, if you want to apply some formulas on only numeric values in a data set, use ISNUMBER function to quickly check the cell location of all numeric values, and then apply the required formulas or functions over them. In this post, we will categorize numeric and text data of a simple data set.
To get started, launch Excel 2010 spreadsheet on which you need to check the data value for text and numeric data type. For instance, we have included a spreadsheet, containing fields including Name, Course, and MarksĀ as shown in the screenshot below.
Now, we will break down our spreadsheet into two categories Text and Non-text. To do so, we will use different functions; ISTEXT, ISNONTEXT, ISNUMBER. The syntax of the these functions are same.
=Function_Name(value)
You just need to pass an argument, which could be the location of the cell containing value or direct value enclosed in double quotation marks. For checking the data type from Name field, we will enter the first cell location of the Name field, which is B2.
=ISTEXT(B2)
As B2 is the location of cell in the Name field, containing values in text format. It will show TRUE for all the corresponding values in this field.
In order to find out all numeric values, we will check the values from Course and Marks fields. As you can see in the screenshot below that it shows TRUE for the location D2 in the function.
By moving plus sign at the end of the cell to left adjacent cell, it will show False at the left side of Marks field, the field Course is present that contains text values.
You can also check out previously reviewed Excel function; Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.
Is ISTEXT working like ISNUMBER when you’re looking into characters in a string? While with ISNUMBER, the operation (–MID(cell, location, length)) is working fine, with ISTEXT it always return FALES, regardles if the specific character is text or number, any idea?