Excel: Keeping Formats when Changing Numbers to Text in Macro

 Last reviewed: November 3, 1994
Article ID: Q79191
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.1 and 3.0
  • Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

Numbers in a worksheet or macro sheet can be converted to text while automatically maintaining the existing number format by using the command macro described below.

MORE INFORMATION

The macro statement shown below will replace the number in the active cell with its text equivalent, while retaining the original number format. 

   =FORMULA(CHAR(61)&CHAR(34)&TEXT(ACTIVE.CELL(),
    IF(GET.CELL(7)="General","#",GET.CELL(7)))&CHAR(34))
Normally, using the TEXT function within the FORMULA function will cause the text to be converted to a number when placed into a worksheet cell. The above formula first gets the number format, applies the format to the text, and then concatenates an equal sign ("CHAR(61)") and double quotation marks ("CHAR(34)") to the beginning and the end to ensure that the text remains as text. As a result, you do not have to specify a format; the existing number format is applied automatically.

REFERENCES

"Microsoft Excel User's Guide," version 3.0, page 113

"Microsoft Excel Function Reference," version 3.0, pages 29, 80, 89-90

KBCategory: kbother
KBSubcategory:

Additional reference words: 2.1 2.10 2.1c 2.10c 2.1d 2.10d 2.2 2.20
2.21 3.0 3.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: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.