Using an Asterisk "*" Character in Replace Command

 Last reviewed: October 31, 1994
Article ID: Q29999
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

SUMMARY

After I typed the following formula, I attempted to replace "*1000" with an empty "With" field: 

   =IF(SUM(M4:M6)<1,NA(),M8*1000)
Doing this should have changed the formula to the following: 

   =IF(SUM(M4:M6)<1,NA(),M8)
However, when the replacement formula was executed, Microsoft Excel deleted everything but the last ")."

MORE INFORMATION

Microsoft Excel uses the asterisk (*) as a wildcard character that can take the place of one or more characters in a string.

In the above Replace command, Microsoft Excel correctly interpreted the "*" as one or more characters preceding the "1000". It replaced these characters with the contents of the "With" field, which in this case was empty.

To have Microsoft Excel interpret the "*" as a regular character in a string instead of as a wildcard character, precede the "*" with a tilde character (~).

The correct form to remove "*1000" from the formula is "~*1000" (without the quotation marks).

KBCategory: kbother
KBSubcategory:

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 31, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.