Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2013, 03:55 PM
jyfuller jyfuller is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2007
Advanced Beginner
Cannot Convert Text Cell to number format to be able to sort the data
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default 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
Attached Files
File Type: xlsx Numbers 2.xlsx (24.7 KB, 14 views)

Last edited by jyfuller; 06-18-2013 at 09:54 PM. Reason: need to add
Reply With Quote
  #2  
Old 06-18-2013, 05:02 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data 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
  #3  
Old 06-18-2013, 05:33 PM
jyfuller jyfuller is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2007
Advanced Beginner
Cannot Convert Text Cell to number format to be able to sort the data
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Numbers 2.xlsx (24.7 KB, 6 views)

Last edited by jyfuller; 06-18-2013 at 09:55 PM.
Reply With Quote
  #4  
Old 06-18-2013, 09:18 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data 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
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.
Attached Files
File Type: xlsx Copy of Book1.1.xlsx (23.6 KB, 11 views)
Reply With Quote
  #5  
Old 06-18-2013, 09:20 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by excelledsoftware View Post
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.
Reply With Quote
  #6  
Old 06-18-2013, 10:03 PM
jyfuller jyfuller is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2007
Advanced Beginner
Cannot Convert Text Cell to number format to be able to sort the data
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 06-18-2013, 10:06 PM
jyfuller jyfuller is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2007
Advanced Beginner
Cannot Convert Text Cell to number format to be able to sort the data
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default

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)?
Reply With Quote
  #8  
Old 06-18-2013, 10:59 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data 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
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)?
I will get that information to you later if needed. When I tested it, I copy and special pasted all the values after the substitute formula. This allowed me to sort it. You probably may need to do the same. If pasting special is not an option we will figure something out.

Let me know if you still need me to re-upload or not.

Thanks
Reply With Quote
  #9  
Old 06-19-2013, 06:55 AM
jyfuller jyfuller is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2007
Advanced Beginner
Cannot Convert Text Cell to number format to be able to sort the data
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 06-19-2013, 05:07 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data 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
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?

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.
Reply With Quote
  #11  
Old 06-19-2013, 05:31 PM
excelledsoftware excelledsoftware is offline Cannot Convert Text Cell to number format to be able to sort the data Windows 7 64bit Cannot Convert Text Cell to number format to be able to sort the data Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

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.
Reply With Quote
Reply

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
Cannot Convert Text Cell to number format to be able to sort the data 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
Cannot Convert Text Cell to number format to be able to sort the data Sort by criteria: number of results p0k Excel 1 10-22-2009 08:33 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft