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.
- Anonymous7 years agoFavorite 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
- ?Lv 47 years ago
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:
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Simple highlight cells needing formatting, then run the above macro, then re-save spreadsheet.