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
- 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.
- 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 :
Technology :
Last Reviewed: March 22, 1999 |