import- 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 


0
sagemaster (106)
4/1/2007 5:21:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1640 Views

Similar Articles

[PageSpeed] 49

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

"Brent" <sagemaster@mvdsl.com> wrote in message news:eOWY7IIdHHA.284@TK2MSFTNGP05.phx.gbl...
> 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



0
4/1/2007 5:37:30 PM
Correction CHAR(160) is the  Required Blank
whereas  CHAR(128) is the Euro symbol.



0
4/1/2007 5:53:23 PM
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.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
news:u8kdPcIdHHA.4888@TK2MSFTNGP06.phx.gbl...
> Correction CHAR(160) is the  Required Blank
> whereas  CHAR(128) is the Euro symbol.
>
>
>

0
ragdyer1 (4060)
4/1/2007 6:18:33 PM
Yes it worked well-  you hint to use the number pad was the key as the 
function key numbers do not work.
Thanks
Brent

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message 
news:eBBu0pIdHHA.4632@TK2MSFTNGP03.phx.gbl...
> 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.
> -- 
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
> news:u8kdPcIdHHA.4888@TK2MSFTNGP06.phx.gbl...
>> Correction CHAR(160) is the  Required Blank
>> whereas  CHAR(128) is the Euro symbol.
>>
>>
>>
> 


0
sagemaster (106)
4/2/2007 4:42:15 PM
Thank you for the feed-back.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Brent" <sagemaster@mvdsl.com> wrote in message 
news:%23UCTkXUdHHA.3632@TK2MSFTNGP02.phx.gbl...
> Yes it worked well-  you hint to use the number pad was the key as the 
> function key numbers do not work.
> Thanks
> Brent
>
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message 
> news:eBBu0pIdHHA.4632@TK2MSFTNGP03.phx.gbl...
>> 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.
>> -- 
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
>> news:u8kdPcIdHHA.4888@TK2MSFTNGP06.phx.gbl...
>>> Correction CHAR(160) is the  Required Blank
>>> whereas  CHAR(128) is the Euro symbol.
>>>
>>>
>>>
>>
>
> 


0
ragdyer1 (4060)
4/2/2007 8:44:41 PM
Reply:

Similar Artilces:

importing Data from access #2
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the data base is " start date", "end date" and "room number". I am also importing " surname" which i have ...

Importing into Oulook beta
Had backup installed on outlook 2003. This transferred to Outlook Beta fine when I transferred. Used it to back-up all Outlook files. Have just re-formatted. Now........ 1. Can't install Outlook Backup from Microsoft site. - it says that Outlook isn't found on my computer. 2. If I try to import the .PST file, (not using Backup) it locks up and after that, the cursor disappears behind Outlook window. Only redemable using system restore. I want to get my folders, mail and settings back. Any ideas? Nick NOTTNICK <nmm457@bellows.org.uk.nospam> wrote: > Now........ > ...

Arranging mail in inbox does not sort properly
When trying to organize items in the inbox by importance you would expect the items after the important items to be listed in chronological order....why doesn't this happen? This is the way it happened in Outlook 98...any help is appreciated.... Go to View | Current View | Customize Current View, click the Sort button, and set your preferred sorting columns and sort order there. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:066e01c3daf3$f0e4f230$a10128...

x-axis is time ... sort of
I'm trying to create a chart in Excel like the one on this page: http://finance.yahoo.com/bonds/composite_bond_rates If the above link doesn't show up, you can get to the page by going to Yahoo Finance and clicking on "Rates", which is about half way down on the left side. The x-axis on this chart is not time as in dates, but it is time as in length of period (e.g. 3 days, 3 months, 3 years...) and the spacing between x-axis labels is scaled by time period (not equally spaced). I can do this in Excel using x-y scatter and x-axis data as # of days (1 for 1 day, 365 for 1 y...

get slide number to start at 1 on slide 4
i need to number my samples so i'm using the slide number as a sample number. problem is my samples start after my index on pg 4 and i need my numbering to start from 1? what can i do "Belinda" <Belinda@discussions.microsoft.com> wrote in message news:B4C58782-7866-438A-A393-45C29BEC6A26@microsoft.com... > i need to number my samples so i'm using the slide number as a sample > number. > problem is my samples start after my index on pg 4 and i need my numbering > to > start from 1? > > what can i do Just drag the slides to th...

Importing Campaign Responses Data in row did not match the import
We are struggling to understand how to importing Campaign Responses using the bulk import wizard. We receive the errors: Reason: Data in row did not match the import type Reason ID: 0x8004032d Please could someone help us diagnose where the problem lies? Thanks Hi, It is very difficult to help you with just this information. We will need some more information to give you helpful suggestions. like whts are the fields, type of file, mapping to which fields and other details. Following URL may be helpful to you. http://www.microsoft.com/dynamics/crm/using/troubleshooting/tsbulkimport.msp...

How import MSWorks spreadsheet to Excel
I am using Exel 2000 and want to impoort a spreadsheet from MSWorks, but I can't seem to find out how. Can't these two MS programs talk to each other? No, they can't talk to each other. It's a Microsoft marketing issue, they don't want Works to compete with Office, so you have to do a "save as" in Works. "BillAtt" wrote: > I am using Exel 2000 and want to impoort a spreadsheet from MSWorks, but I > can't seem to find out how. Can't these two MS programs talk to each other? Thank you; I think that will help me. "dlw" wrot...

Item Import in MS POS
Is it Possible to import Items from CSV or TXT to POS (not RMS) thanks, Yes, there's an inventory import wizard that does this. It's in the manager application - the help guide will give you addl info on this. "Sachafav" wrote: > Is it Possible to import Items from CSV or TXT to POS (not RMS) > > thanks, > ...

Import pst file from Outlook til OE 6.0?
I'm trying to import pst files from Outlook til Outlook xpress 6.0. I get the message "can not import from the MAPI client..." -- Regards Gjermund Unless you also have Outlook on that machine, you won't be able to import the PST files. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza "Gjermund A. Bjordal" <gjermund.bjordal@online.no> wrote in messag...

Recurring Meetings Imported from Domino
This is a multi-part message in MIME format. ------=_NextPart_000_0027_01C583A2.DFFFC8B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Background: Users were migrated from Domino R5 to Exchange 2003 SP1. Problem: When users cancel a recurring meeting that was created in = Domino and imported into Exchange each listed attendee receives multiple = copies of the cancellation, somewhere in the neighborhood of 180 copies. = This behavior is limited to meetings that were imported from Domino. Does anyone have any suggestions as to why th...

Error number: 5
This may not be the best place for this, but here goes: I have several remote users on Exchange 2003 Standard using Outlook 2003, with RPC-over-HTTP. Since there's no "pretty" way to have them change their passwords, every 60 days they are have to log into OWA to change their password there using the IISADMPWD application with IIS 6.0. That'd be great, but when they type out their user name, old password, new password, etc, they are presented with a less-than-explanatory "Error Number: 5." This error shows whether I actually try to fill it out, or if I s...

help with sorting 02-04-10
I have a very large spreadsheet of all of our cases. The headings correspond to the following columns: case # = column A, client # = B, last name = C, first name = D, Social Security# = E, location # = F, case manager # = G, case type = H, additional case identifier (not on all cases) = I, case status = J, and next review date = K. The basic case types that we have are coded either "F", "M", or "A". All clients should have an item for each heading (except for the additional case identifier and Social Security#). So, for example, Joe Blow could hav...

Import Historical Stock Data from Yahoo-Finance
For a long time I thought this was not possible, but I just read a post recently that makes me think that this probably IS possible. Is there a way to import data from finance.yahoo.com, into a table, if you store all stock symbols in a table, and put beginning and ending date in a form (in a couple of TextBoxes)? I know how to do this in Excel, with all stock tickers in a row, like row 4 (or whatever), and all the dates going in descending order in Column A, and each security’s historical prices under the symbols (which are in row 4, for instance). The problem in Access i...

page numbers 12-11-09
Using Word 2007: I want to place a page number at the bottom center of the page using the preformatted choices on the Header/Footer tab. I want to use the one called Scroll. The scroll itself goes fine into the footer, but the page number doesn't print fully inside it, either the top or bottom of the number disappears though the scroll is complete. There are other times, no page number shows up in the scroll and I have no idea how to get one in there! I have this same quandry when using any of the choices that include squares, circles, etc. ...

How to sort colored data
People keep giving me large reports and highlighting the pertinent rows or changing the color of the text. Unfortunately I have now way to sort or filter to consolidate just the row with the red text or cell color. What are my options? Bob See http://cpearson.com/excel/SortByColor.htm In article <3F3CF8FE-3490-4080-B435-483AA4508F26@microsoft.com>, Bob <Bob@discussions.microsoft.com> wrote: > People keep giving me large reports and highlighting the pertinent rows or > changing the color of the text. Unfortunately I have now way to sort or > filter to consoli...

Importing SunOne Calendar Events?
Anyone have luck importing SunOne Calendar items? Our method seems to import them but then they end up with features greyed out if you want to modify them. Thanks ...

hovering cursor over footnote number in text
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I used to be able to hover the cursor over a foot/end note number in the document, and a mini window would show me the note text. All of a sudden, my cursor no longer works this way. I don't think I changed any settings, but can anyone tell me how I might fix this? <br><br>Thanks! Check another document: is that the same? You may have converted that footnote to hard text at some point, in which case there is no note text to show. Sorry: I can't think of anything else... On 11/03/10 5:32 AM, in article...

Auto Generate Serial Numbers for Bill of Materials
Hi! We have a partner in Australia who has some issues with the Serial Number Masks for Finished Goods in Inventory. Currently Bill of Materials functions this way: If the customer creates 20 finished products which tracks Serial Numbers. The user has to manually enter the 20 serial numbers into Assembly Entry. Even though the Finished product has been setup with a Serial Mask, there is no option to Auto Generate Serial Numbers. He would like to suggest that there should be an auto generate serial number function also for the finished goods in Bill of Materials. Thanks, ---------...

Import into Word
We designed in Publisher XP a letterhead. We would like to import this into Word to use as a template. Can anybody pls. help? It just seems impossible for some reason to find any help on this. Thanks so much, z In Publisher, file, save as, files of type, scroll down to whatever image format you have in mind. When you import the image into Word use the graphics tools to crop or if you have saved it as a .wmf, you can ungroup it in Word and delete all the unnecessary background. Can you select the letterhead and copy/paste into Word? -- Mary Sauer MS MVP http://office.microsoft.com/ http:...

Bulk Import of Contacts #2
Urgent!! We have a requirement to automatically map Accounts information with Contacts . Import the mapped data into CRM system using the Bulk Import Tool. As per CRM, we can not import contact and associate to an account automatically using CRM's bulk data import tool. ParentCompanyId field is a GUID and we are trying to associate the Text to that field. If we want to use CRM's bulk import tool, we need to import contact first, and then import account, then associate them manually. And we have thousands of records. Hello Faiz, It sounds like you are using version 3.0, correct...

Page numbers
I'm preparing a publication with "alphabetical" sections. I want to number the pages in each section. Section one, for example, would be number "A-1," "A-2," etc. Is there a way to do this on master pages so I can use the "continued on" and "continued from" pages? This takes a bit of work, but it can be done. What version Publisher? It hard to explain the method. Send me an email and I will send a file that shows the way... mary-sauer at columbus.rr.com -- Mary Sauer http://msauer.mvps.org/ "Alphauthor" &...

Change Number to Text , Case error when cell is String and format is General
Hi All How to check the cell is text or number ? When Cell is string, Case Excel close and recoveing. Sub FormatNum2str() '~~ 2010/02/18 Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim strVal As String If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ic = 1 To iCols For ir = 1 To iRows With Application.Selection.Item(ir, ic) If .NumberFormatLocal = "Gene...

Importing
Good morning I have a tall order and your help would be much appreciated. I always have problems with Ranges properties etc but have got by from finding solutions from the many excellent replies here particularly when it comes to VBA and Macro's. However I now seem to have a problem in finding a suitable solution butif you don't ask ... I have a workbook with several sheets with named employees. At the end of each month I get sent a workbook with details of time spent by each employee on different tasks. Their names appear on the top cell with all their expense details listed below. ...

Same Value Axis Numbers in Four Charts
I'm tracking sixteen separate things, four to a chart ... I have all four charts together on one worksheet ... The values of the tests I'm running range anywhere from 14.50 to 15.50 ... The first chart shows the Value Axis Numbers from 14.30 to 15.40 in increments of .10 ... The second chart shows the Value Axis Numbers from 14.80 to 15.20 in increments of .05 ... How can I make all four charts look the same ...??? Thank you ... CJ Hi, The simplest way is to add a dummy series to all four charts. This series needs to contain the minimum and maximum values to be found across...

Control pasted/imported data to use only ODD or EVEN rows
I want to move data from access 2000 to excel I want to paste or import. May I tell excel to only accept one paste to use ODD rows, the the second paste to use even rows? I have spit data in access queries so I can use two pastes,exports. I have 5000 rows to do per paste. Maybe fifty columns The data in each paste will have identical columns and information. example; Joe, Smith, address, 1st car info (red blue black) Joe, Smith, address, 2nd car info (red blue black) Mary Poppins, address, 1st car info (red blue black) Mary Poppins, address, 2nd car info (red blue black) If you have a s...