Return the number of occurrences of a substring in B1 within a string in A1 : LEN « Text functions « Microsoft Office Excel 2007 Tutorial






  1. Microsoft Office Excel 2007 Tutorial
  2. Text functions
  3. LEN
Input the formula: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)
Input the formula: =(LEN(A1)-LEN(SUBSTITUTE








22.12.LEN
22.12.1.LEN(text), LENB(text) returns the number of characters in a textLEN(text), LENB(text) returns the number of characters in a text
22.12.2.=LENB("ABC")=LENB(
22.12.3.Count the number of Bs (uppercase only) in the stringCount the number of Bs (uppercase only) in the string
22.12.4.Count the number of Bs (both upperand lowercase) in the stringCount the number of Bs (both upperand lowercase) in the string
22.12.5.Return the number of occurrences of a substring in B1 within a string in A1Return the number of occurrences of a substring in B1 within a string in A1
22.12.6.A case-insensitive comparison: Return the number of occurrences of a substring in B1 within a string in A1A case-insensitive comparison: Return the number of occurrences of a substring in B1 within a string in A1
22.12.7.Returns the first nine characters plus an ellipsis for longer stringReturns the first nine characters plus an ellipsis for longer string