#1
|
|||
|
|||
Cannot Convert Text Cell to number format to be able to sort the data
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, P.S. When I convert the "distance" column to from letters to numbers using paste special and then I sort the column from highest to lowest, the numbers do not sort in correct order. They sort to 0, and then they sort from highest to lowest starting from 0 all over again . Please try to sort the distance column after you convert it to numbers; I don't understand why it is sort to 0 and then back up. Jeremy Last edited by jyfuller; 06-18-2013 at 09:54 PM. Reason: need to add |
#2
|
|||
|
|||
Quote:
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 |
#3
|
|||
|
|||
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. Last edited by jyfuller; 06-18-2013 at 09:55 PM. |
#4
|
|||
|
|||
Quote:
Let me know if you have any other questions. |
#5
|
|||
|
|||
Quote:
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
I think you forgot to upload the file you changed with spaces; there is no new spaces and I can't sort. It looks the same to me as the others I posted, nothing is changed. Can you give more detail in the post or in the excel file to explain what you are doing exactly to get rid of the spaces and also how to tell if there are spaces in the cell (character numbers make no sense to me)?
|
#8
|
|||
|
|||
Quote:
Let me know if you still need me to re-upload or not. Thanks |
#9
|
|||
|
|||
Ok. If I do substitute formula with 1 space " " under old text, referring to column J text, in column O, and then in the column next to it, I copy and special paste values, it sorts the column next to it correctly, but this only works if I substitute 1 space; otherwise it sorts like the "bad" column I have.
Can you explain to me why the spaces mess up the sort, even when I copy, paste special, values or multiply the distance column by 1, which has worked in the past? How can I avoid this in the future when I remove numbers from a text string? Also, I don't understand what you mean when you said, "Instead of placing a space in the formula I copy and pasted a space from your distance values". What does that mean? What formula are you referring to? |
#10
|
|||
|
|||
Quote:
No problem this is a fair question. The formula to be used is =substitute(J5," ","")+0 Now here is the situation. It is best explained with an experiment. Open up notepad or wordpad or even word. Type any single word. Right after that word type another word (do not include a space) now place your cursor between those 2 words hold ALT and press 255 the let go of Alt. You will see that it will produce a space. This is not the usual space that is created when you press the space bar it is a char character. go ahead and do ALT and press 2 then let go. Did you get a smiley face? these are char characters. try ALT+217 or ALT+16 they all make different char characters. For some reason the data in column I has Char character (Alt+255) instead of a space. To try this write your substitute formula like so =substitute(j5," Now instead of space press ALT + 255 and then another quote so it looks like this =substitute(j5" " then finish off your formula and try your sort it should work. =substitute(j5" ","",)+0 Sorry for the lengthy explanation. As far as to how to prevent this in the future look to where you copied the data from in column I from it has these char spaces instead of regular spaces. Let me know if this clears it up for you. |
#11
|
|||
|
|||
Last thing. If it is still not sorting for you, be sure to have all of your headers labeled and expand your sort selection. As far as I know Excel will not sort formulas very well if they are referenced to other cells not included in the sort.
|
Thread Tools | |
Display Modes | |
|
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 |