Excel OfficeExcel TEXT functions return a number in a specified number format
If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN. Formula =(LEN(text)-LEN(SUBSTITUTE(text,word,””)))/LEN(word) Explanation In our example, we are using this formula: =(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4) How this formula works B4 is the cell we’re counting words in, and C4… read more »
If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,text,””))) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””))) How the formula works For each cell in the range, SUBSTITUTE removes all… read more »
This tutorial shows how to capitalize first letter in Excel. In Microsoft office word it is called sentence case, in Excel to capitalize the first letter in a word or string, you can use a formula based on the LEFT, MID, and LEN functions. Formula =UPPER(LEFT(A1))&MID(A1,2,LEN(A1)) Explanation In the example shown, the formula in C5 is:… read more »
Using the example below, this tutorial shows how to create Excel Data validation to allow uppercase only. Formula =AND(EXACT(A1,UPPER(A1)),ISTEXT(A1)) Explanation To allow a user to enter only uppercase TEXT, you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions. In the example shown, the data validation applied to C5:C7 is:… read more »
How to convert text to capital letters and more in Excel Often times computer users find it difficult to change case in Excel. Imagine working on a huge volume of excel data and you need to convert a record to capital letters or the reverse. Boom! your stuck because it is not word document. We all… read more »