I had a problem transferring data from another source, excel wasn't recognising that some numbers were numbers and therefore it didn't sort them in numerical order.

Help please!

Dear Maria

Thank you for attending Excel Intermediate Course.

Following function should be able to resolve the problem.

Normally when you copy and paste "numbers from the internet they are copied with extra spaces or non-breaking spaces [Char(160)] attached.

Following should help to resolve the problem:

Please insert a blank new column to the right hand side of the column you want to convert and type the following function to one cell.

=SUM(TRIM(SUBSTITUTE(cell,CHAR(160),"")))

Please note that cell in the above formula should be replaced with the appropriate cell that contains the number you want to convert.

If this posting has helped in answering your query then I would request you to mark the posting as Resolved. If, however, it hasn’t and you need further clarification then please press the reply button and ask for further clarification with your specific question. In case you have a related question then please as the question as a separate posting.


Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003