Thread: [Solved] Cannot Convert Text Cell to number format to be able to sort the data
View Single Post
 
Old 06-18-2013, 05:02 PM
excelledsoftware excelledsoftware is offline Windows 7 64bit Office 2003
IT Specialist
  
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default
Quote:
Originally Posted by jyfuller View Post
Hello,

I have attached a file with the column "distance" where I use the LEFT formula to remove the numbers from the text string adjacent to "distance" column. I want to sort using distance but I cannot convert the cells in the "distance" column to numbers to be able to sort in excel. I have tried paste special-values to convert the cells to numbers, and I have multiplied the left formula in distance column by 1, but I still cannot sort the data and I get a #value when I multiply by 1.

Please tell me what I need to do to convert the cells to numbers to be able to convert. Thanks,

Jeremy
You have spaces in your values for distance. Remove the spaces and then add 0.

Place this formula next to each cell and it should work.
=SUBSTITUTE(J5," ","")+0 [it doesn't seem like the 3 spaces are pasting in that formula for this forum. be sure to place the amount of spaces needed in the first quotes.]

Let me know if this helps.

Thanks
Reply With Quote