 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > I imported this data into a spread sheet- I tried to format it to number, > but when I sort it- I get : [quoted text clipped - 5 lines] > 42.56 > 5.94 Correction CHAR(160) is the Required Blank whereas CHAR(128) is the Euro symbol. If you would prefer a non-code approach, you could try and use, <Edit> <Replace> *AFTER* first identifying the actual character that is appended to your data. With a "bad" number in A1, enter this in B1: =Code(Right(A1)) This should give you the Char() value of the "space", Which is probably Char(160), as David mentioned. Knowing this Char() value, select all the cells containing these "bad" numbers, then: <Edit> <Replace> In the "Find What" window, Hold down <Alt>, and enter 0160 Using the numbers from the Num keypad, *not* the numbers under the function keys. Since this is a <Space>, you won't see anything displayed in the "Find What" window. Don't enter anything in the "Replace With" window. Hit <Replace All> This should remove those non-breaking spaces and convert your data to XL recognizable numbers.  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Correction CHAR(160) is the Required Blank > whereas CHAR(128) is the Euro symbol. Yes it worked well- you hint to use the number pad was the key as the function key numbers do not work. Thanks Brent > If you would prefer a non-code approach, you could try and use, > <Edit> <Replace> [quoted text clipped - 28 lines] >> Correction CHAR(160) is the Required Blank >> whereas CHAR(128) is the Euro symbol. Thank you for the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Yes it worked well- you hint to use the number pad was the key as the > function key numbers do not work. [quoted text clipped - 33 lines] >>> Correction CHAR(160) is the Required Blank >>> whereas CHAR(128) is the Euro symbol.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.