Strips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis : SUBSTITUTE « Text functions « Microsoft Office Excel 2007 Tutorial

```Input the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),":",""),"*",""),"_",""),"(",""),")","")
```

 22.21.SUBSTITUTE 22.21.1. SUBSTITUTE(text,old_text,new_text,instance_num) substitutes new text for old text in a text string 22.21.2. =SUBSTITUTE(A3, "1", "2", 1) 22.21.3. =SUBSTITUTE("a b c f", " ",","): all spaces are replaced with commas. 22.21.4. =SUBSTITUTE("a b c d", " ",",",1): the first space is replaced with a comma 22.21.5. =SUBSTITUTE("a b c d", " ",",",3): the third space is replaced with a comma 22.21.6. =SUBSTITUTE("two two.","two", "three"): both occurrences of two are replaced with three. 22.21.7. =SUBSTITUTE("two two .","two", "three",2): only the second occurrence of two is replaced with three. 22.21.8. =IF(LEFT(A1,1)=" ",SUBSTITUTE(A1," ","",1), A1): If a space is found in the first position, substitute it with an empty string; otherwise, just return the original string. 22.21.9. Use the SUBSTITUTE function to remove all spaces from a string 22.21.10. Strips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis 22.21.11. Returns the number of words