Cannot format cells II

[Zarathrustra]

Tho' I have a similar problem to the post "Cannot format cells", the solution there does not work for me.

I've copied a set of cells across from a stats program. All the data cells are formatable, but not the reference numbers in the first column. I need to be able to re-format these to do a Data Sort. When I do attempt a data sort, it treats this column of data as non-numerical; and the option to sort as if it were numerical only partially sorts numerically.

I'm also not sure how to attach an example to this; I hope I've done it correctly.

Cannot Format Cells.xlsx

[mrice]

Try using the INT function to convert the strings in column A to text and then sorting.

[newdoverman]

I understand it that you want to sort the data in numeric order by the numbers in column A which are "text" numbers.

Enter a 1 in a blank cell and copy it.

Select column A then click on Paste Special, multiply. This will multiply all the values selected in column A by the 1 that you copied. This will change the text numbers to real numbers.

Select the data and sort using column A as the sort key.

[FDibbins]

Another option....
if you look at A3, you will see a small yellow diamond next to it. Highlight all the data in A, scroll back up to A3 (if necessary), click the diamond and select "convert to number"

[Zarathrustra]

Try using the INT function to convert the strings in column A to text and then sorting.

Thanks for responding Martin. However, I don't really understand. INT converts decimalised numbers to integers, which isn't the problem here. Plus I'd have to do this to each entry - which compounds the problem. But thanks for the attempt to help.

[Zarathrustra]

That works! Great. Thanks

[Zarathrustra]

That works too! Great. Thanks guys for all your help. I can move on now.

[mrice]

The INT function will also convert a text string to an integer. By applying the function in the first row and dragging down, you can convert all the cells. Then you can sort by the column containing the function.