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

1. Microsoft Office Excel 2007 Tutorial
2. Text functions
3. SUBSTITUTE

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