LEN function is used to find the total length of text and Substitute function is used to replace one letter with some other letter on all instance. Like If the cell A1 contains text 'Safaraz Ahmed' and I want to find out how many times character 'a' is repeat in cell A1.
1. Type Sarfaraz Ahmed in cell A1
2. Type a in the cell A2.
Click on image below to view Enlarge
3. Enter the following formula in A3: = LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))
SUBSTITUTE(A1, A2,"") will replace 'a' with '' from Sarfaraz ahmed to return Srfrz hmed
which is of length 10 as it also include space.
4. LEN(A1) returns the length text including 'a' which is 14.
5. LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")) returns 4.
Kindly provide your valuable comments.
0 comments:
Post a Comment