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
1038 Views

Similar Articles

[PageSpeed] 11

=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:

Updating Share Point list from within Excel
I have the need to update an existing SharePoint list within Excel using VBA code. Is this possible? Can somebody point me to some reference that can direct met on how to do it? I'm able to save workbook and create dashboard from that workbook. However, now I need to update custom fields in an existing list. Regards, OMER ...

Excel keeps changing my formulas to include nearby cells.
Excel keeps changing my formulas to include nearby cells. More details ... -- Thanks, Shane Devenshire "emeraldc" wrote: > Excel keeps changing my formulas to include nearby cells. ...

How do I calculate the change in stock price in excel.
How do I calculate the change in stock price from the previous day. Put yesterday's price in cell A1 and today's price in cell A2. In cell A3 put the formula: =A2-A1 -- Gary's Student "Knowledge001" wrote: > How do I calculate the change in stock price from the previous day. ...

Conver General number to Currency
I have a column of numbers in Excel 2003 that are of General type. I need to insert a decimal point two positions in from the right of the existing number. When I do that however by using Format > Cells and changing it to Number with 2 decimal places, or to Currency format, it adds a .00 to the existing number instead of inserting a decimal point into the existing number, e.g., 999955 come out as 999955.00 or $999,955.00 when what I really want it to do is 9999.55 or even $9,999.55. Please help! Put 100 in an empty cell, select the numbers, do edit>paste special and select divide, ...

How do you define variables in excel?
I am setting up a spreadsheet to keep track of my students. I want to use excel if possible to keep track of lates, left early, attendance, etc, as my grades are kept there already. I was wondering if you can assign values to variables to accomplish this and how to define them. Thanks Why do you want to assign to variables, why not just use worksheet cells? -- HTH RP (remove nothere from the email address if mailing direct) "BigRon" <BigRon@discussions.microsoft.com> wrote in message news:D246057F-E2D9-474F-85A2-A31440E48142@microsoft.com... > I am setting up a s...

excel formulas #2
Can someone help me with how to create a formula that looks at one cell and based upon the number in that cell (if statement) then gives me another number? example: If cell c4 contains the number 1; then how can I have cell a8 look at cell c4 and give me the number 1 (other than a link) / then have cell a9 look at cell c4 if it contains the number 2 to then give me the number 2.................... I need a formula to look at a cell that will contain a number from 1 to 5 or above and then accoridingly give me a coresponding number to reflect if that number is 1,2,3,4 or 5. Each cel...

Excel should have a "Change Case..." menu Item like Word!!
See the subject, I know you can set up a formula to do it I want a button! PLEASE, for years I've just wanted to change case on the fly, YEARS! ---------------- 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 see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=3c7554c...

Excel 2003 #5
Hi all Got a silly problem. MS Excel 2003 Professional via MS partner pack (aka Action Pack) Start Excel as normal, no problem, clik on FILE on the menu bar and Excel stops responding. ALL other menu items work correctly. I've un-installed and re-installed the software, no change. Excel WILL run in safe mode however. Any ideas anyone please. Thanks in anticipation, Adrian. PS I have an HP psc1215 attached and have noticed some mention of issues with their 'Share-to-web' rubbish that comes supplied (now un-installed) ...

VBA turing up on the web...excel transfer...
Ok, here's the deal! I want to take my database/spreadsheet from excel and place it on the web with interactivity - I have done this so far.....what the problem is: I have macros in VBA programing that is not going with the excel pages onto the web...why? Is this going to be easier if I start from scratch in FrontPage to be able to control this program fully? Please help...I can't find anyone that knows....:-( ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** ...

Using labels in EXCEL 2007
I have app 400 names in Col B row 1 down to row 400 of my spreadsheet. I would like to transfer that info to "paper labels" Anyone know how? Thanks "pcor" <pcor@discussions.microsoft.com> wrote in message news:F98982B9-75BD-4A9D-85AD-2C28BC9408EA@microsoft.com... >I have app 400 names in Col B row 1 down to row 400 of my spreadsheet. > I would like to transfer that info to "paper labels" > Anyone know how? > Thanks You ask "Anyone know how" Of course someone here knows how! I don't have your answer, b...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

How do I select multiple rows randomly in MS Excel?
How do I select multiple rows randomly in MS Excel? Hi Select your first row and then hold down the Ctrl key while selecting the others. HTH Michael "Varun" wrote: > How do I select multiple rows randomly in MS Excel? "Varun" <Varun@discussions.microsoft.com> wrote > How do I select multiple rows randomly in MS Excel? Just another angle to the post's interp .. (with emphasis on "randomly") Here's an example set-up to play with .. Assume we have 6 rows of data below in Sheet1's A1:C6: Data1 Text1 Desc1 Data2 Text2 Desc2 Data3 Text...

Can you sort a column by text going right to left?
I am trying to sort a by column, but want the sort to start with the right side of the column-for example 1234gk-want to sort by kg4321 or really just gk, but I thought I could get right to left easier. I don't want to go thru the whole sheet to delete the numbers If all your data is in that format in an unused column you could enter the formula: =Right(A1,2) Then copy down and sort on this new column Hope this helps Rowan luvsdogz wrote: > I am trying to sort a by column, but want the sort to start with the right > side of the column-for example > 1234gk-want to sort by kg...

Adding a string to a text document at the cursor.
I'm using VC++ 6. I've created a small text editor derived from CEditView to which I've added a drop-down list of phrases off the main menu bar to be inserted into the text at the cursor when a phrase is clicked. I can't figure out how to insert the selected phrase into the text I'm editing. I also would like to know if there is a way to keep the drop-down list open after clicking on the first phrase, closing it only with 'ESC' or moving to another heading on the main menu like 'File'. Any leads greatly appreciated. >I'm using VC++ 6. I've...

How do I combine 2 text columns in Microsoft Excel?
I have two columns of descriptive text, the second column is the end of the first column's sentence, however I can not find a way in the Help options to combine the text values to create one complete sentence in one column. Does anyone know of a way to do this? Try: =A5&" "&B5 if you want spaces between the columns or look at the CONCATENATE function example =CONCATENATE(A5,B5) Domaniman wrote: > I have two columns of descriptive text, the second column is the end of the > first column's sentence, however I can not find a way in the Help options to ...

looking for a script to make a Distribution List from a text file
Does anyone have a script for making a new Distribution List from a text file, or CSV file? I created larger dist lists by using adduser. Then, once the group is created, I mail enable it. "BwiseIT" wrote: > Does anyone have a script for making a new Distribution List from a text > file, or CSV file? > > > ...

No account number shown in printed checks
Using Money 2007 Deluxe. Printed a series of checks from MS Mmoney this morning, but the account numbers didn't print on the checks. Each of the payees has an account number entered in the appropriate place, in the "Go to Payees" detail listing. Can't figure out what's going on here! Thanks for any assistance. Dave On Sat, 17 Mar 2007 08:37:03 -0700, Dave M. <DaveM@discussions.microsoft.com> wrote: >Using Money 2007 Deluxe. > >Printed a series of checks from MS Mmoney this morning, but the account >numbers didn't print on the checks. Each of...

Query Text in the Mid of a memo
I am trying to query off a memo field [Description] the text that follows "User Name: " until the first space the "User Name: " is not in the same position nor is the text of the user name a fixed size. I am working with an InStr function but I don't know how to stop at the " " after the name. Thanks so much for any help. AngieSD wrote: >I am trying to query off a memo field [Description] the text that follows >"User Name: " until the first space the "User Name: " is not in the same >position nor is the text of the user n...

email attatchment containing original message
please help every email i receive contains an attatchment which is a notepad page containing the email message too. Stuart, at least 3 of us today have had this problem. I solved this problem on my machine by the simple expedient of rebooting! Before rebooting, I had deleted all cookies, temporary internet files and files in windows/temp, as this was suggested in response to another posting (evidently this helped similar problems in Outlook Express). This latter did nothing, but the reboot did. You might have to do both. Deleting temporary internet files and cookies is done in I...

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

Excel Formula Help
I need to create a spreadsheet to calculate my weekly payroll. The timecards are punched in a timeclock which prints out the in and out times. for example, in at 7:30 am out at 5:00 pm. It also gives me a running total of the number of hours accumulated. I want to take the total number of hours and minutes and convert that to a decimal. If an employee has 32:35 minutes total time accumulated, then using a formula in an excel spreadsheet, I want to see these hours in decimal format, i.e., 32.58 Anyone know how to help me do this? Thanks! might want to try microsoft.public.excel Mat...

text dropped when printing
Sometimes when using several lines of text in a cell with "wrap text" the last line is dropped when printed, it shows on the monitor but does not print. Any idea why this is happening? Thanks. Are you using a true-type font? "Carol" <cstan@att.net> wrote in message news:058101c371b5$bab74ca0$a401280a@phx.gbl... > Sometimes when using several lines of text in a cell > with "wrap text" the last line is dropped when printed, > it shows on the monitor but does not print. Any idea why > this is happening? Thanks. ...

Split text cell into seperate colums without splitting up a word
I have text cells with sentences ranging from 0 to 160 characters long. I want to break these into 40 character chunks (in separate cells), but don't want to split any word in half. ie, if the 40 char mark is in the middle of the work, I want to go backwards, find where the word starts and split from that point. It's exactly like a wrap text -- but I want to split those lines up into separate cells. thanks kaf If your sentence were in A1, use these 4 formulas: B1: =LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:40")),1)=" ")*ROW (INDIRECT("1:40")))) C1: =LEF...

Web link to Excel file
I'm trying to link to an Excel 97 file from a ColdFusion web page. The first time I click the link, the file opens fine in the browser (I.E. 5.5). If I close the file and click the link again, I get a message stating 'filename.xls is currently being modified by "my user name here". Open as read only?'. I can still open the file as read only, but am wondering why I get this message, because I'm not making any modifications. I've had other people try this link, and they get the same thing, except it tells them that they're the one modifying the fi...