Microsoft KB Archive/58786

Numeric Values May Be Treated as Text Last reviewed: June 1, 1995 Article ID: Q58786

The information in this article applies to: Microsoft Excel for Windows, versions 2.x, 3.0, 4.0 Microsoft Excel for OS/2, versions 2.2 and 3.0 SUMMARY Numeric values imported from other applications may be treated as text by Microsoft Excel. To correct this problem, activate each cell containing a "text number" and reenter it (press F2 and then ENTER). MORE INFORMATION To convert a selected range of cells, select a range of cells containing the values to be converted, and then use either of the following macros. Sample Macro 1 A1: Convert A2: =ERROR(TRUE,MACRO1.XLM!$A$6) A3: =REFTEXT(ACTIVE.CELL()) A4: =IF(LEFT(GET.CELL(6),1)="=",GOTO(A6)) A5: =FORMULA(VALUE(ACTIVE.CELL()),ACTIVE.CELL()) A6: =SELECT(,"RC[1]") A7: =IF(REFTEXT(ACTIVE.CELL())<>A3,GOTO(A5),RETURN()) NOTE: This macro will have no effect on cells containing true text or formulas. The macro assumes that it is located on a macro sheet named MACRO1.XLM; if your macro sheet name differs, modify the macro sheet name in cell A2 accordingly. Do not use this macro on a single cell. Sample Macro 2 A1: ConvertTextToNumber A2: =FOR.CELL("CurrentCell",SELECTION(),FALSE) A3: =SELECT(CurrentCell) A4: =FORMULA(GET.CELL(5,CurrentCell)*1) A5: =SELECT("r[1]c") A6: =NEXT() A7: =RETURN() Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose. For additional information, please see the following article(s) in the Microsoft Knowledge Base: ARTICLE-ID: Q75945 TITLE  : Converting Text to Numbers in Excel

KBCategory: kbusage 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 Last reviewed: June 1, 1995 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.