Microsoft KB Archive/78339

{|
 * width="100%"|

Excel: Arguments to the SUBSTITUTE Function Are Case Sensitive

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2 and 3.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

-

SUMMARY
When using the SUBSTITUTE function in a Microsoft Excel macro or on a worksheet, one of the required parameters, old_text, is case sensitive.

MORE INFORMATION
The structure of the SUBSTITUTE function, which substitutes new_text for old_text in a text string, is:

  =SUBSTITUTE(text,old_text,new_text,instance_num)

If text is capitalized, but old_text is not, no substitution will occur and no error will be reported.

Steps to Reproduce Problem

 * 1) Type the following formula on a worksheet:

A1:=SUBSTITUTE(&quot;Sales Data&quot;,&quot;sales&quot;,&quot;Cost&quot;)

&quot;Sales Data&quot; is returned. No substitution has occurred and no error is reported. The same behavior occurs in a macro.
 * 1) Change the formula as follows:

A1:=SUBSTITUTE(&quot;Sales Data&quot;,&quot;Sales&quot;,&quot;Cost&quot;)

The formula will now correctly substitute &quot;Sales&quot; with &quot;Cost&quot; and return &quot;Cost Data&quot;.