Todays ms excel lesson 05-12-2011 (advance strings function)

 

Counting The Number Of Specific Characters Or Strings Of Characters In A Cell

The following formula will return the number of times that the text in B1 occurs in the text in A1. This is not case sensitive so, for example,  ‘A’ is treated the same as ‘a’.

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),”")))/LEN(B1))

If you want to use a case-sensitve match where for example ‘A’ is treated differently than ‘a’, use the following formula:

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,”")))/LEN(B1)) 

Counting The Number Of Letters In A Cell

The following formula counts the number of letters (A to Z, in either upper or lower case) in cell A1.

=IF(LEN(A1)=0,0,SUM((CODE(UPPER(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))>=CODE(“A”))*(CODE(UPPER(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))<=CODE(“Z”))))

This formula is an array formulas so you must press CTRL SHIFT ENTER rather than just ENTERwhen you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. 

 

Click here Continue Reading 

Replies (1)

Great Post Sir.. Keep Sharing

               

 

                           

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
Featured 07 November 2026
Chartered Accountant

DPV & Associates

Chennai

CA

View Details
Company
Featured 29 October 2026
Accounts Executive

Synergy Keystone

Chennai

B.Com

View Details
Company
30 October 2026
Asst Manager Accounts

Goodwin Pumps India Pvt Ltd

Chengalpattu

CA Inter

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
13 November 2026
Chartered Accountant - Fresher

Neeraj Bhagat & Co

New Delhi

CA

View Details
Company
ARTICLESHIP 20 November 2026
Article clerk

Jain Sanjay Kumar & Co

Jaipur

CA Inter

View Details
Company
ARTICLESHIP 14 November 2026
Article Assistant

Jitendra Agarwal & Associates

Bengaluru

CA Inter

View Details
Company
Featured 17 November 2026
Chartered Accountant

M/s. A V C & Co.

New Delhi

CA

View Details