=SUBSTITUTE("a b c f", " ",","): all spaces are replaced with commas. : SUBSTITUTE « Text functions « Microsoft Office Excel 2007 Tutorial






Input the formula: =SUBSTITUTE("a b c f", " ",",")
Input the formula: =SUBSTITUTE








22.21.SUBSTITUTE
22.21.1.SUBSTITUTE(text,old_text,new_text,instance_num) substitutes new text for old text in a text stringSUBSTITUTE(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)=SUBSTITUTE(A3,
22.21.3.=SUBSTITUTE("a b c f", " ",","): all spaces are replaced with commas.=SUBSTITUTE(
22.21.4.=SUBSTITUTE("a b c d", " ",",",1): the first space is replaced with a comma=SUBSTITUTE(
22.21.5.=SUBSTITUTE("a b c d", " ",",",3): the third space is replaced with a comma=SUBSTITUTE(
22.21.6.=SUBSTITUTE("two two.","two", "three"): both occurrences of two are replaced with three.=SUBSTITUTE(
22.21.7.=SUBSTITUTE("two two .","two", "three",2): only the second occurrence of two is replaced with three.=SUBSTITUTE(
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.=IF(LEFT(A1,1)=
22.21.9.Use the SUBSTITUTE function to remove all spaces from a stringUse the SUBSTITUTE function to remove all spaces from a string
22.21.10.Strips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesisStrips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis
22.21.11.Returns the number of wordsReturns the number of words