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 27 October 2026
semi-qualified CA

Saini Pati Shah & Co LLP, Chartered Accountants

Mumbai

CA Final

View Details
Company
ARTICLESHIP 23 October 2026
CA Articleship / Article Assistant

M N Rao & Associates LLP

Vijayawada

CA Inter

View Details
Company
Featured 07 November 2026
Paid Assisstant

DPV & Associates

Chennai

CA Inter

View Details
Company
ARTICLESHIP 17 November 2026
Vacancy for Articleship

Promptitude Accounting Services Private limited

Bengaluru

B.Com

View Details
Company
30 October 2026
Asst Manager Accounts

Goodwin Pumps India Pvt Ltd

Chengalpattu

CA Inter

View Details
Company
ARTICLESHIP 25 October 2026
Article Intern (CA Articleship)

Arpan Bohra & Co

Hyderabad

CA Inter

View Details
Company
ARTICLESHIP 16 November 2026
Audit Executives

Loonker Godawat And Company

Bengaluru

B.Com

View Details
Company
ARTICLESHIP 20 October 2026
CA ARTICLE

AMKN & CO

Noida

CA Inter

View Details