Excel: Arguments to the SUBSTITUTE Function Are Case Sensitive

ID: Q78339

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("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
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.