COUNTA Formula Excel with Example.

How to use COUNTA Formula in MS Excel / COUNTA Formula With Example/ Function with Example in Microsoft Excel.

Every Excel user should have knowledge / information about COUNT Function, as COUNTA Formula is a very important formula in MS Excel. In this Example we will learn What exactly COUNTA Formula do in Excel or How to use COUNTA Formula in MS Excel? Also what is the Difference between COUNTA & COUNT Formula?

COUNTA Function or COUNTA Formula counts the number of cells in a range that have data in them. If you+ want to count the number of cells that contains data, then COUNTA function / formula will be used. COUNTA Formula can be used in Microsoft Excel 2003, 2007, 2010, 2013 or any version of Microsoft Office MS Excel.

Difference between COUNTA & COUNT Formula in Excel:-

As we already discuss about COUNT Formula, COUNT Formula counts cells only that contains numbers not any other data but COUNTA formula counts all the cells that contains data either the data is numeric (Number) or any other data type.

What is the SYNTAX of COUNTA Formula in Excel?

SYNTAX:- =COUNTA(range) or =COUNTA(value1, value2....valueN)

NOTE:- COUNTA counts all cells that have any data, if you want to count only cells that contains numeric (number) values then use COUNT formula instead of COUNTA.

COUNTA Example:-

We have a data that contains some data doesn't matter what is the type of data either it's numeric or any other type, COUNTA formula will simply count the cells that contains any type of data. Below is an example:-

COUNTA Formula Excel

Please give your suggestions and comments in comment box. Thanks.

Read More

TRIM Formula with Example MS Excel

TRIM Formula Excel / How to use Trim Function in MS Excel / Use of TRIM Formula in Microsoft Excel Spreadsheet / TRIM Example Excel.

There is a Formula in Microsoft Excel Software, TRIM Formula. TRIM Formula is a very interesting and useful formula in MS Excel. Here we will learn how to use TRIM Formula with Example? In this Formula we will use LEN Formula for the better understanding of TRIM Formula.

Where we use TRIM Formula, How to use TRIM Formula in Excel and What is the TRIM Formula Syntax in Microsoft Excel Software?

TRIM Formula Syntax:-  =TRIM(text) or =TRIM(cell reference)

Sometimes we get double spaces in our data and we wants to get rid of any double space in a cell, except for single spaces between words. There are some situations when you pull data from a database or you get data from other sources and for some reason extra spaces are put in behind or in front of proper data, in that case TRIM formula is extremely useful. So, to remove extra spaces (more than one space in a cell), spaces after the data or spaces before the data TRIM function is used.

Example of TRIM Formula:-

We have a sentence in cell B2 (Just for an example) and sentence has more than one space in between the sentence and once space before the starting of the sentence and one space after the sentence, so to remove all more than 1 space in between the sentence, spaces before sentence and spaces after the sentence, we will use TRIM formula in cell D3 and Result in D4.

TRIM Formula Image

To know how TRIM Formula works in Excel worksheet, we will use LEN Formula to know the character count before the TRIM Formula and to know the character count after TRIM Formula.
Know about LEN Formula in MS Excel, CLICK HERE.

Read More

COUNT Formula Excel / How to use COUNT Function in MS Excel.

COUNT Formula with Example in Microsoft Excel / How to use COUNT Formula in MS Excel / Microsoft Excel (MS Office) COUNT Function / Formula.

In MS Excel, COUNT Formula is a very important formula. Every Excel user should have knowledge / information about COUNT Function. What exactly COUNT Formula do in Excel or How to use COUNT Formula in MS Excel with Example?

COUNT Function or COUNT Formula counts the number of cells in a range that have numbers in them. If you want to count the number of cells that contains data* (only Numeric Data), then COUNT function / formula is used. COUNT Formula can use in Microsoft Excel 2003, 2007, 2010, 2013 or any version of Microsoft Office MS Excel.

What is the SYNTAX of COUNT Formula in Excel?

SYNTAX:- =COUNT(range) or =COUNT(value1, value2....valueN)

NOTE:- Remember that COUNT Formula in MS Excel Counts only those cells that contain Numbers (Numeric Data).

COUNT Example:-

We have a data and we want to know the count of number cells within the data range. Lets say, We have some data from A2 to A11 (A2:A11) and we want to know the count of cells that contains numbers, then we will use COUNT Formula. See below:-

Count Example

In Example 1, there are two values which are not numbers so COUNT Formula count only 8 rather than 10.

In Example 2, All values are numbers (Numeric values) so COUNT Formula counts complete cells within the range E2:E11.

Read More

LEN Formula with Example in MS Excel

How to Find Lenght of Characters in a cell in MS Excel? Len Function Microsoft Excel. Get No of Characters in a cell in Excel. LEN Formula with Example in Excel 2007.



Len Formula / Function is a very useful formula in MS Excel. Len Function gives the character lenght or we can say counts the character in a cell in MS Excel. The LEN Formula counts the characters present in a cell with spaces in Microsoft Excel. Sometimes we need to put a specific lenght of characters in a data, in that case we need find the number of characters / letters in Excel cells, then we use LEN Function.

LEN Formula in MS Excel:- =LEN(CellValue)

LEN Formula with Example:-

We have some addresses in cell A2:A9 and we want to find out the lenght of characters present in these cells.

Write the LEN formula (=LEN(A2)) in cell B2 and press Enter.

Now Fill the formula till the end of the data (A9), and you will get the count of characters available in cells.

NOTE:- LEN Formula counts all the spaces and special characters too.

Read More

Formulas: String Functions

STRING FUNCTIONS:

To remove all non printable characters.	CLEAN	=CLEAN(text)
To combine two or more strings.	CONCATENATE	=CONCATENATE(text1, text2)
		to combine any specific word use "" (inverted commas) like "excel".
To extract a substring from a string starting from the left-most character.	LEFT	=LEFT(text, no of characters)
To find the length of any specified string.	LEN	=LEN(text)
To convert all the letters in lowercase.	LOWER	=LOWER(text)
To convert all the letters in uppercase.	UPPER	=UPPER(text)
To set the first character in each word to Uppercase	PROPER	=PROPER(text)
To remove all leading and trailing spaces.	TRIM	=Trim(text)
To extract a substring from a string between two specific numbers.	MID	=MID(text, start_position, number_of_characters)
To replace a set of characters with another from a string.	SUBSTITUTE	=SUBSTITUTE(text, old_text, new_text, [starting position])

Read More