Non-numeric Data

[Guest]

Hi All,
I'm trying to run descriptive statistics on data that has been copied and
pasted from a .csv file but it says some of the data is non numeric.
i've used the view cell contents download, i've tried trim and clean, i've
tried multiplying everything by 1, all without success and i'm going mad here.
Does anyone have any ideas?
Thanks in anticipation,
Angela

 

[Pete]

You could enter the formula =VALUE(A1) in a spare column, Format the
cell to Number with appropriate decimal places, and copy the formula
down. Do this for the other columns of data. Fix the values of the
formulae by highlighting them all and using <Copy>, Edit | Paste
Special | Values | OK and <enter> Then you can delete all the original
columns.

Pete

 

[Guest]

Thank you- this works for some cells but not all. It works for cells that are
truly empty but some seem to have something in them even after i try to
remove all contents. does that make sense?

 

[Jerry W. Lewis]

What do you get from =CODE(cellAddress)

Jerry

Thank you- this works for some cells but not all. It works for cells that are
truly empty but some seem to have something in them even after i try to
remove all contents. does that make sense?

:

 

[Danny@Kendal]

Hi All,
I'm trying to run descriptive statistics on data that has been copied and
pasted from a .csv file but it says some of the data is non numeric.
i've used the view cell contents download, i've tried trim and clean, i've
tried multiplying everything by 1, all without success and i'm going mad
here.

Try opening the CSV file directly in a new spreadsheet. There should be an
option to choose the format of the columns.

Alternatively select the imported data then choose Edit -> Goto -> Special
then select Constants and uncheck all the options other than "Numbers".
That should select only the numerical values. While they are selected choose
a different colour for the text and background so any non-numerical cells
will stand out.

Are you using the correct range to work out the statistics?

 

[Guest]

mostly 2-digit integers: 48,32,50,51,45 but some return #VALUE!

 

[Guest]

the 'go to/special' tip is good but what do i do with the non-numerical data
when i highlight it? These are spreadsheets with 65000 rows of data by 20
columns so manually deleting cells is not an option.

 

[Danny@Kendal]

the 'go to/special' tip is good but what do i do with the non-numerical
data
when i highlight it? These are spreadsheets with 65000 rows of data by 20
columns so manually deleting cells is not an option.

Hmmm. Can you find just a sample of the invalid data and are you able to see
why it's invalid?

 

[Guest]

The problem seems to be that I have cells which look empty but some of them
contain spaces. When I use trim to clear out the spaces, it still doesn't
solve the problem. I don't know if it is because not all the blank cells are
blank in the same sense

 

[Danny@Kendal]

The problem seems to be that I have cells which look empty but some of
them
contain spaces. When I use trim to clear out the spaces, it still doesn't
solve the problem. I don't know if it is because not all the blank cells
are
blank in the same sense

Use Find & Replace to erase spaces:
In the "Find" box type a space, in the "Replace" box just delete whatever is
in there to leave it empty. That should scrub all spaces in your numerical
data, including cells with one space in them. Make sure "find entire cells
only" is unchecked.

If that doesn't work then it shouldn't be too hard to write a macro to scan
through the worksheet looking for non-numerical data in the selected cells.

 

[Guest]

Thanks Danny- that sounds just what I'm looking for.
Angela

 

[Gord Dibben]

Angela

Could be you have the HTML non-breaking space Character 160

See David McRitchie's TrimAll code.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord Dibben Excel MVP

 

[Jerry W. Lewis]

32 is a space character
48-57 are digits, but those cells should not look empty.
#VALUE! is a truly empty cell

None of these should be problematic for the functions (AVERAGE, STDEV,
etc) that calculate descriptive statistics. However, the spaces would
be problematic for the ATP Descriptive Statistics tool.

Elsewhere in the thread, you indicate that you tried to remove spaces
with the TRIM() function. That would produce a zero length string, not
an empty cell. ATP will not tolerate strings, even those of zero
length. You will have to actually remove the cell contents.

Jerry