Friday, September 11, 2015

How to use excel formula to get dynamic results


Here, we will see how we can get dynamic output with a simple function.

For example, assume that you have an Excel sheet having some 2000 web domain names ending with forward slash "\"  


How will you remove that forward slash "\" from the list?
Using LEFT command, we can get the result.


Syntax
=LEFT(text,[num_chars])

text = > Select the cell data
num_chars  = > Number of characters you want to fetch

Domain

 =LEFT(A2,24)
24 = Number of characters in the cell.

In =LEFT(A2,24) number 24 has to change based on cell length


By using another simple formula, LEN, you can find the number of characters in the cell.

Syntax=LEN(text) = It will tell us number of characters in a cell

Now, using both LEFT and LEN, we can get dynamic results.







Result


  


Thursday, January 22, 2015


Count The Number Of Cells With Text In Excel



      If you need to count a text in Microsoft Excel, what we do?

      We know =count ( ) formula  to count the number of cells with Contain number.

      But count the cells with Contain TEXT?

          = COUNTA ( )
    
    What Does It Do?
    This function counts the number of numeric or text entries in a list. 
    It will ignore blanks.
    
     Syntax      = COUNTA (Range1, Range2, Range3 ... through to Range30) 


    Example 1

      The following table show, how to count total no. of cell contain TEXT
                   =COUNTA( ) - COUNT( )

            

 Example 2
The following table was used by a school to keep track of the examinations taken by students
Each exam passed was graded as 1, 2 or 3.
A failure was entered as Fail.
The school needed to known how many students sat for each exam.
The school Also needed to know how many exams were taken by students .

The = COUNTA () function has been used because of its ability to count text and numeric entries.





Twitter Bird Gadget