Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Thanked 0 Times in 0 Posts

    Cell Values used in formulas or conditions (2000)

    I have a XLS spreadsheet exported from an ERP app. It contains various values including zeros. However, if a conditional formula like '=IF(A1=0,TRUE,FALSE)' is used, the values that are 0 do not return True. If conditional formatting of =0 or <0.1 is used, these cells do not meet the condition. If I type '0' directly in Excel, it works.

    In all other respects including formatting, the values derived from export and those entered direcly look and behave same. How do I make these values work like numbers not text?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Cell Values used in formulas or conditions (2000)

    Alas, this is a well-known problem for files exported from other applications.

    For a single column of cells, you can use the following technique:
    <UL><LI>Select the cells,
    <LI>select Data | Text to Columns...,
    <LI>Click Finish.[/list]Another method, that will work for more than one column at a time:
    <UL><LI>Select an (arbitrary) empty cell,
    <LI>Copy to clipboard (Ctrl+C),
    <LI>Select the range of cells,
    <LI>Select Edit | Paste Special...,
    <LI>Click Add, then OK.[/list]These methods should convert values that are erroneously being interpreted as text to numbers.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Tampico, Tamps, Mexico
    Thanked 0 Times in 0 Posts

    Re: Cell Values used in formulas or conditions (2000)

    Hi Hans

    The second procedure is good, but I believe that it would be good, to warn, that he/she has to take care of with the columns that is dates, because they lose the format when this procedure is used,and is necessary to put the format "date" to the corresponding columns.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    Re: Cell Values used in formulas or conditions (2000)

    Manually (this will seem counterintuitive) you can convert them column by column by selecting one column and using Data, Text-to-Columns, Fixed Width, Next, Finish. Or by VBA you can run this on the entire selected area (it may take a while with large areas):

    Sub Text2Values()
    Application.ScreenUpdating = False
    Dim rngOpOn As Range, rngCell As Range
    Set rngOpOn = Intersect(ActiveSheet.UsedRange, _
    Selection.SpecialCells(xlCellTypeConstants, 3))
    If Not rngOpOn Is Nothing Then
    For Each rngCell In rngOpOn
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts