1

I have an Excel column that had a list of ages, with several empty cells more or less evenly distributed along the column. I tried to use the Data Analysis Toolkit add-in to get Descriptive Statistics for the Age column, but got a message that the column contains non-numeric data. I then used an IF function to output 0 when an Age cell is blank, and I copied the function output column, then pasted values, to ensure nothing could think the new column contained anything non-numeric. The column now looks like this, and contains only numeric values and no formula:

enter image description here

My main concern here is that the non-zero values look like text values, and Excel always treats text values as text, even if they only contain numerics. What have I done wrong or not done in trying to make this Num Age column only numeric? What I did was select all 812 cells in the column only, and apply the following format:

enter image description here

I really expect every cell in that column to be numeric, yet the left aligned, non-zero numbers behave like text values (left align), where only the zero values behave like numerics, and the Data Analysis Toolkit won't give me Descriptive Statistics because the column contains non-numeric values.

ProfK
  • 2,741

2 Answers2

1

Just found way to convert text to numbers with relative ease. Highlight the column that currently has your numbers stored as text. Then, click the Text-to-columns button on the data tab. Then click FINISH. It should automatically fix your cell format.

Here, column A is the before, while column C is the impact of using this trick.

enter image description here

Isolated
  • 1,535
  • 1
  • 5
  • 6
1

Text to Columns in probably your best best (as it doesn't impact blank cells), but some other options depending on what you working on.

Highlight the data of interest. Anyways click on the little box that comes up in the corner and convert to number.

Another trick is in another cell enter 1. Click on this cell then press Ctrl&C. Highlight the cells right click and select Paste Special.

enter image description here

Then select multiple and Ok.

enter image description here enter image description here

The other fairly quick option is in another column add formula multiplying the value in the first by 1. Then copy and paste values back over.

Hopefully, this adds to your Excel tool box.

BradR
  • 772