SUBSTITUTE

=SUBSTITUTE(text,old_text,new_text,instance_num)

Arguments:

  • text
    • This can be text or a cell reference
  • old_text
    • What you want to replace.
  • new_text
    • What you want to substitute for the old_text.
  • instance_num (optional)
    • You can opt to substitute only one occurrence

Q: How can I count the number of characters in a cell excluding spaces?

A: Example:

  • In cell A1 is the string "abc d e fg"
  • In cell B1 is a formula to return the total number of characters in the cell.
    • =LEN(A1)
  • In B2 is a formula to count the number of characters excluding spaces.
    • =LEN(SUBSTITUTE(A1," ",""))

 
   Missing: Microsoft Office Web Components
 
This page requires the Microsoft Office Web Components.

Click here to install Microsoft Office Web Components..

This page also requires Microsoft Internet Explorer 4.01 (SP-1) or higher.

Click here to install the latest Internet Explorer.
 

You need Office Spreadsheet components installed to see the example.

Published: 06-Feb-2004
Last edited: 05-Jun-2005 19:16