promotion image of download ymail app
Promoted

How to remove #Num! error in Microsoft Access?

I have a large text file which couldn't be opened in Excel because it has more rows than Excel could open. I opened it in Access which replaces some of the cell values (numbers) and puts #Num!. I need to do some analysis on that data and I don't know how to remove this error. I am sure that the cells are not blank because I opened my file in Notepad++ which shows all numbers but I can't do analysis within it.

2 Answers

Relevance
  • Anonymous
    7 years ago
    Favorite Answer

    use the import wizard when you import into Access

    that way you can specify the datatype of the Excel column

    you should specify that it's text even though you think it's a number

    you'll get rid of #NUM, but the column will be text

    but at least you'll be able to begin manipulating the "bad numbers" and fix them

    • Commenter avatarLogin to reply the answers
  • ?
    Lv 4
    7 years ago

    Hi,

    You have a rather awkward problem here given you say you cannot open your data in Excel.

    The #Num! in Access is telling you the cell contains a mix of data; some digitit, some text, for example: 34G would throw a #Num!

    You need to go back to the Excel and change the format of any cell containing both numbers and text to "mixed data" then save that sheet before importing to Access.

    You can do this with a Macro:

    Sub Addspace()

      Dim cell As Object

      For Each cell In Selection

        cell.Value = " " & cell.Value

        cell.Value = Right(cell.Value, Len(cell.Value) - 1)

      Next

    End Sub

    Simple highlight cells needing formatting, then run the above macro, then re-save spreadsheet.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.