sorting numbers and numbers that contain text in excel

A column contains both strictly numbers  and also numbers that are followed 
by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a)
Identical numbers are related documents, with the text suffixes referring to 
addenda documents; thus, document 1000 has an addendum document 1000a; How 
can I sort the column so in the following order: row 1 (1000), row 3 (1000a), 
row 2 (1500), row 4 (1500a)?
Thank you
-- 
MZ
0
Utf
11/24/2009 9:55:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
672 Views

Similar Articles

[PageSpeed] 51

=TEXT(A1,"0") will turn each into text, then sort by that helper column (and 
don't accept Excel's suggestion to treat text that looks like numbers as 
numbers).
--
David Biddulph

"MZ" <MZ@discussions.microsoft.com> wrote in message 
news:C73994B3-7BD6-4738-BFC4-5A71FAF48A18@microsoft.com...
>A column contains both strictly numbers  and also numbers that are followed
> by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a)
> Identical numbers are related documents, with the text suffixes referring 
> to
> addenda documents; thus, document 1000 has an addendum document 1000a; How
> can I sort the column so in the following order: row 1 (1000), row 3 
> (1000a),
> row 2 (1500), row 4 (1500a)?
> Thank you
> -- 
> MZ 


0
David
11/24/2009 10:56:48 AM
Thank you for the reply. I had already converted all the numbers into a text 
format, yet it does not help. 
-- 
MZ


"David Biddulph" wrote:

> =TEXT(A1,"0") will turn each into text, then sort by that helper column (and 
> don't accept Excel's suggestion to treat text that looks like numbers as 
> numbers).
> --
> David Biddulph
> 
> "MZ" <MZ@discussions.microsoft.com> wrote in message 
> news:C73994B3-7BD6-4738-BFC4-5A71FAF48A18@microsoft.com...
> >A column contains both strictly numbers  and also numbers that are followed
> > by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a)
> > Identical numbers are related documents, with the text suffixes referring 
> > to
> > addenda documents; thus, document 1000 has an addendum document 1000a; How
> > can I sort the column so in the following order: row 1 (1000), row 3 
> > (1000a),
> > row 2 (1500), row 4 (1500a)?
> > Thank you
> > -- 
> > MZ 
> 
> 
> .
> 
0
Utf
11/25/2009 7:37:01 AM
Are you sure that you converted the contents of the cell to text?  How did 
you do it?
Or did you merely change the format of the DISPLAY to text (which has no 
effect on the cell contents)?
What does =ISTEXT(A2) say (& for other rows)?
If they really are all text but they don't sort correctly, perhaps you have 
stray spaces or other non-printing characters?  Does =LEN(A2) [and 
correspondingly for other rows] show the length you expect for the text 
string in the cell?
--
David Biddulph

"MZ" <MZ@discussions.microsoft.com> wrote in message 
news:6CC47179-54BB-485B-985C-4AD4953BC02B@microsoft.com...
> Thank you for the reply. I had already converted all the numbers into a 
> text
> format, yet it does not help.
> -- 
> MZ
>
>
> "David Biddulph" wrote:
>
>> =TEXT(A1,"0") will turn each into text, then sort by that helper column 
>> (and
>> don't accept Excel's suggestion to treat text that looks like numbers as
>> numbers).
>> --
>> David Biddulph
>>
>> "MZ" <MZ@discussions.microsoft.com> wrote in message
>> news:C73994B3-7BD6-4738-BFC4-5A71FAF48A18@microsoft.com...
>> >A column contains both strictly numbers  and also numbers that are 
>> >followed
>> > by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a)
>> > Identical numbers are related documents, with the text suffixes 
>> > referring
>> > to
>> > addenda documents; thus, document 1000 has an addendum document 1000a; 
>> > How
>> > can I sort the column so in the following order: row 1 (1000), row 3
>> > (1000a),
>> > row 2 (1500), row 4 (1500a)?
>> > Thank you
>> > -- 
>> > MZ
>>
>>
>> .
>> 


0
David
11/25/2009 7:45:53 AM
Reply:

Similar Artilces:

Replacing the Numbers
In my report I want to replace the value of my text data to a word. Example: in my report the TextBox: is (loc) which the "1", "2", "3" these are the records in the tables. They print out on the reports as 1, 2, and 3. I want to change that to read 1=ED, 2=EU and 3=EZ. And blank, if the user leave it blank as "NONE" I hope that's clear Thanks Check out your more recent thread with the same question. -- Duane Hookom MS Access MVP "bladelock" <bladelock@discussions.microsoft.com> wrote in message news:1FB81F68-1733-4174-8D...

Cannot open Excel attachments
Hello, I have a user that cannot open Excel attachments from inside Outlook. He gets a security message and is given an option to save the attachments or cancel. He can open all other attachments without this warning appearing. I checked his registry and he has .xls files in the exception that is posted on the MS KB. He has Excel 2002 and Outlook 2002. His e-mail comes through an Exchange server. I do not have special security settings in place for this user. I cannot find anything else on Google or MS. Please help. Thank you. Aaron Aaron hu kiteb: > Hello, I have a user that cannot op...

Outlook 2002 Contact Phone Number Field Formatting
Why don't phone numbers entered in the phone number contact fields on Outlook 2002 automatically format to include the paren's and hyphens? If this is suppose to happen, what is the setting to make it happen? They do here and always have. What are you seeing? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer mike <michael.kelner@fmr.com> asked: | Why don't phone numbers entered in the phone number | contact fields on Outlook 2002 automatically format to | include th...

Where's the Sort by New Message option?
Not sure when the layout of the newsgroups changed but I can't seem to find the ability to sort by new message in a thread instead of sorting by just the original thread date. That was hugely helpful as I could follow along and see what threads were still active. If you're gonna hang around in the newsgroups (any newsgroups), you may want to start using a newsreader. Microsoft Outlook Express can do it for you. Saved from a previous post: You may want to connect to the ms newsserver directly: If you have Outlook Express installed, try clicking on these links (or copy and paste i...

Sorting
Hi, is it possible to use variable for sorting? Something like: declare @sort varchar(10) SET @sort='column1 DESC' SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table Is dynamic SQL only option? Thank you, Simon On 15/04/2010 11:28, simon wrote: > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? You can use CASE to he...

How to sort account list?
I'm using MS Money 2004 Small Business. In the account list, my accounts are catagorized by and in this order: - bank accounts - credit accounts - investment accounts - loans and liabilities - asset accounts TOTAL BALANCE In the above view, I cannot easily determine totals for assets and liabilities. The following view does this. - bank accounts - investment accounts - asset accounts SUBTOTAL -loans and liabilities SUBTOTAL TOTAL BALANCE How do I get the above view? Thanks, Brett I don't use Small Business. But you can run a Net Worth report to get total assets and ...

How to prevent ole container hwnd receive certain messages? #2
I used CSubclassWnd of Paul DiLascia to subclass(in fact use SetWindowLong to replace WindowProc) hwnd returned from IOleWindow::GetWindow. In the WindowProc I prevent it by LRESULT CShellEmbedding::WindowProc(UINT msg, WPARAM wp, LPARAM lp) { if (msg == WM_WINDOWPOSCHANGING) return 0; return CSubclassWnd::WindowProc(msg, wp, lp); // Important!! } but spy++ still tell me that hwnd of ole get the send WM_WINDOWPOSCHANGING message and return. So Is it right way to eat message in win32 or ole container hwnd does special things? "Su Wei" <suwei@sina_com> wrote in message new...

looking for Excel VBA work
I am looking for Excel VBA work... I have been working with excel and vba for the past 3 years. I am very involved in it. I love helping people. Although I do require some money for my work, I do not charge as high as most programmer's do. If you need some help or some work done, please contact me at conspiringfate@earthlink.net Mike -- conspiringfate ------------------------------------------------------------------------ conspiringfate's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31745 View this thread: http://www.excelforum.com/showthread.php?...

Row names will not move with sort
Each row in my summary table in Sheet1 is hyperlinked to a corresponding table in Sheet2 with additional details. I need to move the rows in my summary table (Sheet1) and preserve the links to and from the details tables (Sheet2). I found that I can move rows in Sheet1 with cut & insert and the hyperlinks are maintained. I can travel from a row in summary table (Sheet1) to details table (Sheet2) and back, even after the corresponding row (Sheet1) was moved. But if I sort the rows in Sheet1 the links from Sheet2 back to corresponding rows in Sheet1 lead to presorted locations. Apparen...

Calculate sum of product in Excel 2003
Hi Guys, Sorry to bother you. I just couldn't find a way to solve it. Here is the scenario, A B 1 2 3 2 2 4 3 4 3 4 5 4 I need to find the sum of product in the column A and B. I can do this by using a third column. I simply find the products then sum them. I need to do this in one formula, not another column. I can do this by =a1*b1+a2*b2+....... The problem is the columns have over 50 values. Manually writing that would be real cumbersome. Any suggestion? Mosaddeq One of the few times that this is be...

I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. If if needs to stay a number with the same value, I don't think you can do it. If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: > I need to format the number cell so that it will sti...

Text to Number conversion
Hi all, Need some help. One of my main data for my job, is in Excel format. The problem lies with the cell format. All number being stored as text. I need to do error checking and then keep pressing C on the keyboard ( for converting to number ) . It took between 30 to 45 minutes pressing time before it finally completed. Is there any way to remove this "Pressing Job" as it really irritates me. I don't blame you ...... Following from Excel Help:- 1.. Select a blank cell that you know has the General number format.=20 2.. In the cell, type 1, and then press ENTER.=20 ...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

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

Receiving excel files
Whenever I receive an Excel file through email and try to open it, I receive the error message 'file format is not valid'. These are all .xls files. Any suggestions on how to fix this problem ??? Doug What versions of XL? (if you have versions before 97 and you are sent 97 or above, the file formats are different) -- HTH Nick Hodge Southampton, England nick_hodge@btinternet.com "Doug" <dwire90@hotmail.com> wrote in message news:01ae01c3903a$31837eb0$a301280a@phx.gbl... > Whenever I receive an Excel file through email and try to > open it, I receive the...

sorting macro #3
I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a time but I know there must be an easier way. Can ANYONE HELP? C3:J3 I need these cells sorted and then down to C1532:J1532 I did create a macro to sort after I selected them but still one at a time. Does it have to be a macro? I can edit a macro but don't really know how to program them. Yes, it has to be a macro. The following should do it. This macro loops through all the entries in Column C, and in each row it sorts the values in Columns C:J. HTH Otto Sub SortRows() Dim rColC As Range Dim i As...

text in cell shows up as ####
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Text in cells in a coumn shows up as ####, apparently because there is a large amount of text. When I mouse over, the text shows. It also shows up in the formula bar. How can I copy and or print the text in these cell? <br><br>When I try to save these cells as text and transfer to Word, they continue to print as ###. text in cell shows up as #### <br> I am totally new to this whole environment! A new iMAC and this software. <br> However, in trying to learn how to use EXCEL, I found that...

Excel should allow macro on a shared workbook.
We are using excel tables all the time at my company. There are more than 20 people working on the same excel file which has thousands of columns so we use autofilter or some other macros. Shared workbook will help us alot because we will be able to change the file at the same time which is very time saving. Is there any way that macros are allowed at shared workbooks? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not ...

Can we sort a form and also be able to enter new records.
Hi, It is possible to sort the data on a form and also be able to enter new and edit the existing records in it. Cheers! On Tue, 15 Jan 2008 15:41:16 -0800 (PST), Rex wrote: > Hi, > > It is possible to sort the data on a form and also be able to enter > new and edit the existing records in it. > > Cheers! I assume you mean "Is it". The form sort order has nothing to do with editing or adding new records, so the answer is yes. If you cannot edit or add new records, make sure the form's record source, if it is a query, is updateable. Some queries are not. O...

Save Excel 2002 to be read by MacIntosh Excel 2004?
Is it possible for the MacIntosh Excel 2004 to read an Excel XP 2002 spreadsheet? How should XP spreadsheet be saved? HELP, please!! MacXL98/01/v.X/2004 files are the exact same format as WinXL97/00/02/03 files. Save the file as an XL workbook and transfer it to the Mac. In article <0D5EBD4E-E12F-4E2D-AC2C-F45E556284E9@microsoft.com>, "cagedbirdflies" <cagedbirdflies@discussions.microsoft.com> wrote: > Is it possible for the MacIntosh Excel 2004 to read an Excel XP 2002 > spreadsheet? How should XP spreadsheet be saved? HELP, please!! "JE McGimpse...

Sorting checking register
How do i sort my check register in Money 2004 Deluxe so that uncleared transactions appear below cleared transactions? In Quicken i could click on the column to provide that sort. In microsoft.public.money, Dean wrote: >How do i sort my check register in Money 2004 Deluxe so >that uncleared transactions appear below cleared >transactions? In Quicken i could click on the column to >provide that sort. You don't. Available choices are chosen by clicking View at the top of the register. ...

Is it possible to sequentially number the comments field
Anyone out there clever enough to tell me how to sequentially populate the comments field in cells... the problem I can see is that the cells are in a grid of A5-10 through to i5 to 10, I want them populating a5,a6,a7,a8,a9,a10,b5,b6 etc and when I get to i10 I want the sequence to carry over to the next sheet... Don't want much do I ;) TIA Nick It looks like rather than "sequentially" populating the Comment fields, you are simply putting the cell's address into the Comment field. If that observation is correct, then I do not know what you mean when you said "when ...

using addrress books to sort incoming mail
I use address books to sort incoming mail. An odd thing: depending on how the sender's name comes across, it doesn't get caught by the filter (amd moved to the "Family Mail" Folder I have set up. for example: my mom and dad's email (from momanddad@somedomain.com) comes as from that address. However, in my contacts, they are listed by their names: John and Mary Smith, with momanddad@somedomain.com as their email address and John and Mary Smith as the display name. What have I done wrong? TIA 1. The big ONE - failed to post your version of Outlook. Rules operate at ...

Reusing SBMnn Numbers and Locating SBM files
I have added and deleted various companies in SBM. Each add uses the next SBM number. When I delete a company that number is removed. Is there a way to recover and re-use a number? Is it possible to have different SBM companies on different drives? Fred Fred, Next newsgroup down. This is the M$ Retail Management Point of Sale group. -- * "Fred" <Fred@discussions.microsoft.com> wrote in message news:A2454A5E-956F-4EF1-BCDA-A5A4B8C0457B@microsoft.com... I have added and deleted various companies in SBM. Each add uses the next SBM number. When I delete a company that numbe...

Printing page numbers in report writer reports.
Dear All, Is there any way to print the page numbers in reports like 1 of 10, 2 of 10,...............10 of 10 format instead of 1,2,3,.................10. Any help would be greatly appreciated! Thanks! -- Developer Hi Habeeb Sadly this is not possible with the Dexterity Report Writer. The Report Writer is a single pass report writer which means it will not know how many pages are printed until it has printed them, by which time it is too late to put the "of #" on the page. If you used VBA with RetrieveGlobals.dll. ADO and SQL to access the tables and estimate the numbe...