#### Sort numbers with text in ascending order

```I am having a difficult time sorting the following list:

MY List	    How Excel Sorts	      I Need
2148-00	     1472-00	              171B-00
1480C-00    1480B-00                  171C-00
1480B-00    1480C-00	              1472-00
1472-00	     171B-00	              2148-00
171C-00	     171C-00	             1480B-00
171B-00	     2148-00	             1480C-00

I have tried formatting numbers as text and other options.  I am at my

Tania
```
 0
Tvouk (2)
10/1/2004 10:40:00 PM
excel.misc 78881 articles. 5 followers.

4 Replies
363 Views

Similar Articles

[PageSpeed] 11

```TVOUK

If you can identify that then a sort may be possible!  It should be numeric
or alphabetical somewhere within the string.

John

<Tvouk@yahoo.com> wrote in message
>I am having a difficult time sorting the following list:
>
> MY List     How Excel Sorts       I Need
> 2148-00      1472-00               171B-00
> 1480C-00    1480B-00                  171C-00
> 1480B-00    1480C-00               1472-00
> 1472-00      171B-00               2148-00
> 171C-00      171C-00              1480B-00
> 171B-00      2148-00              1480C-00
>
>
> I have tried formatting numbers as text and other options.  I am at my
>
> Tania

```
 0
jp1 (93)
10/2/2004 11:20:23 AM
```Any chance that you made a mistake in your message and wanted the 2148-00 sorted
to the bottom?

171B-00
171C-00
1472-00
1480B-00
1480C-00
2148-00

If that's true, then I'd insert 3 helper columns to be used to extract the
leading numeric portion from the trailing text.

Say your data was in A1:A???
In B1, put this:
=MATCH(TRUE,(ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)
but hit ctrl-shift-enter instead of just enter.
For 171B-00,it returns a 4 (first position of a non-number)

In C1, put this:
=--LEFT(A1,B1-1)
it just returns 171 (as a number)

In D1, put this:
=MID(A1,B1,LEN(A1))
It returns B-00

And sort first by column C, then by column D.

Tvouk@yahoo.com wrote:
>
> I am having a difficult time sorting the following list:
>
> MY List     How Excel Sorts           I Need
> 2148-00      1472-00                  171B-00
> 1480C-00    1480B-00                  171C-00
> 1480B-00    1480C-00                  1472-00
> 1472-00      171B-00                  2148-00
> 171C-00      171C-00                 1480B-00
> 171B-00      2148-00                 1480C-00
>
> I have tried formatting numbers as text and other options.  I am at my
>
> Tania

--

Dave Peterson
ec35720@msn.com
```
 0
ec35720 (10082)
10/2/2004 2:15:05 PM
```Thank you sooooooooooooooooooo much!!!!!  It worked!!!!!!!!

Tania

Dave Peterson <ec35720@msn.com> wrote in message news:<415EB7E9.A200881A@msn.com>...
> Any chance that you made a mistake in your message and wanted the 2148-00 sorted
> to the bottom?
>
> 171B-00
> 171C-00
> 1472-00
> 1480B-00
> 1480C-00
> 2148-00
>
> If that's true, then I'd insert 3 helper columns to be used to extract the
> leading numeric portion from the trailing text.
>
> Say your data was in A1:A???
> In B1, put this:
> =MATCH(TRUE,(ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)
> but hit ctrl-shift-enter instead of just enter.
> For 171B-00,it returns a 4 (first position of a non-number)
>
> In C1, put this:
> =--LEFT(A1,B1-1)
> it just returns 171 (as a number)
>
> In D1, put this:
> =MID(A1,B1,LEN(A1))
> It returns B-00
>
> Now select your range A1:D???
>
> And sort first by column C, then by column D.
>
>
>
> Tvouk@yahoo.com wrote:
> >
> > I am having a difficult time sorting the following list:
> >
> > MY List     How Excel Sorts           I Need
> > 2148-00      1472-00                  171B-00
> > 1480C-00    1480B-00                  171C-00
> > 1480B-00    1480C-00                  1472-00
> > 1472-00      171B-00                  2148-00
> > 171C-00      171C-00                 1480B-00
> > 171B-00      2148-00                 1480C-00
> >
> > I have tried formatting numbers as text and other options.  I am at my
> >
> > Tania
```
 0
Tvouk (2)
10/8/2004 12:10:33 AM
```Woohoo!

Tvouk@yahoo.com wrote:
>
> Thank you sooooooooooooooooooo much!!!!!  It worked!!!!!!!!
>
> I really appreciate your help!!!!!
>
> Tania
>
<<snipped>>
```
 0
ec35720 (10082)
10/8/2004 12:52:51 AM

Similar Artilces:

Payroll Batch Number
Is it possible to obtain the batch number of a transaction after it has been posted? I have been asked to create a report for our payroll department that lists the batch number in addition to the transaction detail that is contained in UPR30300. Thank you, Jeff Majchrzak No, the batch number is not available for reporting. -- Charles Allen, MVP "Jeff Majchrzak" wrote: > Is it possible to obtain the batch number of a transaction after it has been > posted? I have been asked to create a report for our payroll department that > lists the bat...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Filter records that include certain text strings
I want to create customized reports where a user can enter text strings, then the program will search for all records that contain that text string and display them. The filter functions are exclusive. -- W Dean Welch On Jul 6, 6:34 pm, Dean Welch <wdeanwe...@bellsouth.net> wrote: > I want to create customized reports where a user can enter text strings, then > the program will search for all records that contain that text string and > display them. The filter functions are exclusive. > -- > W Dean Welch In the same field? Just pass a filter in the open event of the ...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =\$C\$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

if statement with text
I am trying to create an if statement where the string of text contains total, if yes then perform calculation base on that row and column. if no total in text leave blank help =IF(ISERROR(FIND("Total", A1)),"",-yourCalculation-) FIND returns #VALUE if "Total" is not in A1 Therefore, ISERROR returns False if "Total" is in A1 (i.e., it was found, no error) HTH, -- George Nicholson Remove 'Junk' from return address. "jerry" <jerry@discussions.microsoft.com> wrote in message news:E249057A-F6E4-45BF-929A-B2BB61C3A700@micro...

z-order of various lines and bars in an Excel 2007 chart
What is the procedure for changing the z-order of various lines and bars in an Excel 2007 chart, such as drop lines, high-low lines, up/ down bars, and error bars? I observed that when I add drop lines to an Excel 2007 chart, they appear in front of the underlying data -- in my case, the underlying data is displayed as a plotted area. I want the plotted area to be fully visible with no lines over it. I would like to send the drop lines to the back and keep the plotted area in the front, but there is no option to do this. So, is there a procedure for adjusting the z-order of drop...

convert text in excel to uppercase
Is it possible to convert all text in a workbook or on a spreadsheet to all uppercase? In VBA Editor Sub MakeUpper() Dim MySht As Worksheet, MyCell As Range For Each MySht In ThisWorkbook.Sheets For Each MyCell In MySht.UsedRange.Cells MyCell = UCase(MyCell) Next Next End Sub "elaine" wrote: > Is it possible to convert all text in a workbook or on a spreadsheet to all > uppercase? Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany "elaine" <elaine@discussions.microsoft.com> s...

Numbering Recurring Items #2
Hey all, Is it possible to number recurring items such as tasks and appointments? I have several recurring appointments for lectures, and would like to show them as "Lecture 1" in week/occurence 01, "Lecture 2" in week/occurance 2, etc. Thanks, Murdoc -- Murdoc ## ------------------------ "The journey of 1000 miles begins with a broken fan belt and a leaky tyre." "Duct tape is like The Force. It has a light side and a dark side, and it holds the universe together." "If the facts don't fit the theory, change the facts." -Albert Einstein &...

Excel devides every number by 100
Even when I open a brand new file. I input a number (ie 5) and get 0.05 back. I have tried doing everything in cell format etc.. anyone have an idea? it is very annoying and adding loads of work to my project. thanks "=?Utf-8?B?a2luZ3M=?=" <kings@discussions.microsoft.com> wrote in news:48A39EFC-2D09-420F-B2DF-6EF5C0DB6109@microsoft.com: > Even when I open a brand new file. I input a number (ie 5) > and get 0.05 back. I have tried doing everything in cell > format etc.. anyone have an idea? it is very annoying and > adding loads of work to my project. > ...

How do i sort rows randomly?
I want to choose 50 random rows from 10,000 lines of data and paste it into a new sheet. The only way I know is to use a random number generator to randomly select the records and then copy/paste the data out out, row by row, fifty times, which is time-consuming. Is there a way to randomize my entire data table by row so that I can take the first fifty rows all at once and know that they've been randomly selected? Thanks. Jeremy Jeremy wrote: > I want to choose 50 random rows from 10,000 lines of data and paste it into a > new sheet. The only way I know is to use a random numb...

page numbering
I want to change the way the date appears in the footer and header. Currently the date is day/month/year. I want month/day/year. setting the date to a particular format is easy in a cell. How do I set the date format in the header and footer? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ jamessem, you could use a macro like this to do it Sub test() With ActiveSheet.PageSetup .LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub ...

Getting same number to appear over and again
Hi. I'm trying to change the E's I have entered in a column to 1's. However, in lieu of changing them one by one, I would like to do them all in one fell swoop. Any suggestions? John Hi John You could use find and replace. Select all of your data. Goto Edit>Find Find what:- E Replace with:- 1 HTH Martin John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel group. And do a google search for newsgroup etiquette!! John Do NOT multipost. See your answer in Excel ...

Importing Purchase orders with integration manager.
When I import a purchase order in Excel, GP creates a separate po for each line item. How do I get the line items under one PO number? I notice that the SQL option does not allow you to have a query relationship. You will need two source tables, one with PO header info and one with line information. Use two sources in Integration Manager and relate them by a common field (like PO Number) If I remember correctly, there is a sample integration provided just like this. -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users For help learning and better using Dyna...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...

Halfway through my publisher document the text starts scrambling
I am in the middle of a Publisher document that I have to get out ASAP and the words are scrmbling all of a sudden; for instance instead of the it prints out eht. HELP!! Might try an updated printer driver. What version Publisher are you using? What printer? What version Windows? Canon has issues with Publisher 98 and Windows 98 How to troubleshoot text printing (inkjet printers) in Publisher 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;198258&Product=pub PUB2000: Troubleshooting Text Printing (Laser Printers) http://support.microsoft.com/default.aspx?scid=kb;en-us;198...

Repeat statements "x" number of times...
Hi All, I have the following code at the end of a long macro: Columns("D:D").Find(What:="total").Select ActiveCell.Offset(0, 4).Range("A1").Select ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])" ActiveCell.NumberFormat = "0.00" ActiveCell.Font.Bold = True With ActiveCell.Interior .ColorIndex = 6 End With What I am doing is when the word "total" appears, it will go to the cell 4 columns to the right, and then do a formula and format the cell. Question: How can I get this to repeat itself for eac...

Find a text
Hi all I am trying to find a text and delate a Row if the text is in the row. But I am not sure how to do it. Can someone help me? Option Explicit Sub FindText() Dim Cell As Range For Each Cell In ActiveSheet.UsedRange If Cell = "Samtals hreyfing:" Then ‚the text Samtals hreyfing sin in in the column E:E ‚If the text Samtals hreyfing: is in the row then I want to delete the Row End If Next Cell End Sub I think you may be looking for this... Sub FindText() Dim Cell As Range For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E")) ...

Help with formula: finding text
Hi, I would like to ask for help with a formula for comparing name in a cell with a list of names in a table. If there is a match it should return a associated text to the matched name from the table. If no match it should just leave the cell blank. Thanks in advance Jonas Hi =VLOOKUP(A1,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "Jonas Ornborg" wrote: > Hi, > I would like to ask for help with a formula for comparing name in a cell > with a list of names in a table. If there is a match...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...

Export (or save as) .csv with text delimiters
Has anyone found a way of coercing Excel 2000 to export .csv files with delimiter text? e.g: as "Mickey Mouse","Minnie Mouse","Donald Duck" not Mickey Mouse,Minnie Mouse,Donald Duck Thanks Caroline -- CarolineP ------------------------------------------------------------------------ CarolineP's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20007 View this thread: http://www.excelforum.com/showthread.php?threadid=345941 http://www.mcgimpsey.com/excel/textfiles.html -- Regards, Peo Sjoblom "CarolineP" <Car...