Cannot Convert Text Cell to number format to be able to sort the data

excelledsoftware · #2 06-18-2013, 05:02 PM

Quote:

Originally Posted by jyfuller

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to format text in a CELL?	Learner7	Excel	3	08-06-2012 03:52 AM
Sort Data	rkeles	Excel	6	02-04-2012 01:31 PM
How can I convert pptx to text format like Word, and later on create pptx too?	GetLost	PowerPoint	0	01-11-2012 04:23 AM
Convert Number to Text	devcon	Word	0	07-10-2010 01:16 AM
Sort by criteria: number of results	p0k	Excel	1	10-22-2009 08:33 PM