Excel: Arguments to the SUBSTITUTE Function Are Case Sensitive

ID: Q78339

The information in this article applies to:

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("Sales Data","sales","Cost")

    "Sales Data" is returned. No substitution has occurred and no error is reported. The same behavior occurs in a macro.


  2. Change the formula as follows:

    A1:=SUBSTITUTE("Sales Data","Sales","Cost")


The formula will now correctly substitute "Sales" with "Cost" and return "Cost Data".


REFERENCES

"Function Reference," version 4.0, pages 422, 432-433

"Microsoft Excel Function Reference," version 3.0, pages 231, 235-236

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 docerr 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 22, 1999