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

excelledsoftware · #1 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

jyfuller · #2 06-18-2013, 05:33 PM

I have attached the file again. I tried what you said and it is still not sorting. When it sorts it sorts to 0 and then back up. wierd.....

It says it is a numeric value but when I multiply by 1 I get #value. I am really confused please help.

excelledsoftware · #3 06-18-2013, 09:18 PM

Quote:

Originally Posted by jyfuller

I have attached the file again. I tried what you said and it is still not sorting. When it sorts it sorts to 0 and then back up. wierd.....

It says it is a numeric value but when I multiply by 1 I get #value. I am really confused please help.

Ok I am not entirely sure why but it appears that the spaces in your original distance are actually char characters and not spaces. I have attached the corrected values. Instead of placing a space in the formula I copy and pasted a space from your distance values. It may be Char 255 or 256 again not sure. Using the attached will sort.

Let me know if you have any other questions.

excelledsoftware · #4 06-18-2013, 09:20 PM

Quote:

Originally Posted by excelledsoftware

Ok I am not entirely why but it appears that the spaces in your original distance are actually char characters and not spaces. I have attached the corrected values. Instead of placing a space in the formula I copy and pasted a space from your distance values. It may be Char 255 or 256 again not sure. Using the attached will sort.

Let me know if you have any other questions.

The last thing on the char character may be if you copy and pasted the distance values from somewhere or imported them. In any case the substitute formula is working with the space copied and pasted.

jyfuller · #5 06-18-2013, 10:03 PM

Yea,

I still don't get it and can't sort, but its okay. I don't understand what you are saying and it still is not sorting.

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