Converting a Specialized Number Format with RIGHT() in Excel

Last reviewed: November 4, 1994
Article ID: Q62726

SUMMARY

Formulas that contain special codes that, for example, determine the number of trailing zeros in a numeric value can be converted to real numbers with the RIGHT() or LEFT() Excel function.

An example of one type of specialized format that has been used is a several-digit number, where the first digit represents the number of zeros that follow the other digits. The cell A1 may contain a number such as 6380. The first digit, 6, indicates the number of zeros after the remaining digits, 380. Thus 6380 actually represents the value 380,000,000.

To convert the number format of 6380 to 380,000,000, use the following formula:

   =VALUE(RIGHT(A1,3))*10^VALUE(LEFT(A1,1))

Additional reference words: 2.0 2.00 2.01 2.1 2.10 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 4, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.