Alphanumeric sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-2862624%22%20slang%3D%22en-US%22%3EAlphanumeric%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2862624%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20that%20are%20connected%20and%20I%20want%20to%20sort%20by%20the%20second%20column%2C%20I%20want%20it%20sorted%20numerically%20but%20it%20keeps%20moving%20the%20numbers%20with%20the%20-1631%20to%20the%20bottom%20of%20the%20list%20how%20do%20I%20fix%20this%3F%3C%2FP%3E%3CP%3EI%20have%20other%20columns%20on%20the%20same%20spreadsheet%20that%20I%20sort%20and%20I%20get%20a%20sort%20warning%20asking%20to%20read%20as%20numbers%20and%20I%20click%20this%20and%20it%20sorts%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20has%20Dollars%3C%2FP%3E%3CP%3EColumn%20B%20has%26nbsp%3B%2049900%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049815-1631%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049816-1631%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20it%20to%20read%26nbsp%3B%2049815-1631%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049816-1631%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049900%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2050020-1631%3C%2FP%3E%3CP%3Eand%20I%20want%20to%20be%20able%20to%20sort%20this%20while%20sorting%20Column%20A%20so%20it%20stays%20together.%20I%20have%20multiple%20columns%20on%20this%20spreadsheet%20that%20have%20two%20columns%20that%20are%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2862624%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2863446%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2863446%22%20slang%3D%22en-US%22%3EThis%20is%20a%20guess.%3CBR%20%2F%3EBut%20I%20don't%20think%20Excel%20can%20read%2049816-1631%20as%20a%20number.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2864136%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2864136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BHi.%20You%20will%20have%20to%20interpret%20the%20part%20before%20the%20minus%20sign%20as%20numeric.%3C%2FP%3E%3CP%3Ec4%26nbsp%3B%3DIFERROR(NUMBERVALUE(LEFT(%5B%40Data%5D%3BFIND(%22-%22%3B%5B%40Data%5D)-1))%3B%5B%40Data%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFIND(%22-%22%3B%5B%40Data%5D)-1)%3C%2FP%3E%3CP%3EFind%20the%20minus%20and%20substract%20one%20to%20find%20the%20numnur%20of%20digits.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELEFT(%5B%40Data%5D%3BFIND(%22-%22%3B%5B%40Data%5D)-1))%3C%2FP%3E%3CP%3EGet%20that%20number%20of%20characters%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENUMBERVALUE(LEFT(%5B%40Data%5D%3BFIND(%22-%22%3B%5B%40Data%5D)-1))%3C%2FP%3E%3CP%3ETurn%20it%20into%20a%20numeric%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIFERROR(NUMBERVALUE(LEFT(%5B%40Data%5D%3BFIND(%22-%22%3B%5B%40Data%5D)-1))%3B%5B%40Data%5D)%3C%2FP%3E%3CP%3EIf%20there%20is%20no%20minus%20FIND%20will%20generate%20an%20error.%20If%20so%2C%20return%20the%20value%20as%20is%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_1-1634714729748.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318634i3834A6CA152910B4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bosinander_1-1634714729748.png%22%20alt%3D%22bosinander_1-1634714729748.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20D%20and%20E%20are%20optional%20depending%20on%20your%20need.%3C%2FP%3E%3CP%3Ed4%20%3DIFERROR(NUMBERVALUE(RIGHT(%5B%40Data%5D%3BLEN(%5B%40Data%5D)-FIND(%22-%22%3B%5B%40Data%5D)))%3B0)%3C%2FP%3E%3CP%3Ee4%26nbsp%3B%3D%5B%40%5BNum%201st%5D%5D-%5B%40%5BNum%202nd%5D%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5B%40Data%5D%20means%20same%20row%20in%20pyamas%20tables%20field%20Data.%3CBR%20%2F%3EIf%20using%20an%20ordinary%20range%2C%20replace%26nbsp%3B%5B%40Data%5D%20with%20B4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGenerally%2C%20I%20prefer%20using%20MID%20since%20it's%20one%20function%20that%20can%20be%20used%20also%20for%20left%20and%20right.%20In%20the%20attached%20file%20though%2C%20LEFT%20and%20RIGHT%20are%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2870533%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2870533%22%20slang%3D%22en-US%22%3EHere%20is%20picture%20of%20my%20spreadsheet.%20One%20column%20is%20the%20dollar%20amount%20the%20other%20column%20is%20an%20invoice%20number%20and%20I%20need%20the%20invoice%20number%20in%20numerical%20order%20so%20I%20highlight%20the%202%20columns%20under%20the%20vendor%20to%20sort%20by%20the%20invoice%20number.%3CBR%20%2F%3E%3CBR%20%2F%3ENortheastern%20Refrigeration%20OP%20Aquatics%20The%20Plumbing%20Source%20Rinaldi%20Landscape%3CBR%20%2F%3E%3CBR%20%2F%3EAmount%20Inv.%20%23%20Amount%20Inv.%20%23%20Amount%20Inv.%20%23%20Amount%20Inv%20%23%3CBR%20%2F%3E%3CBR%20%2F%3E%24(1%2C940.00)%201377712142%20%24(631.50)%201197576-000%20%24(355.00)%20212807%20%24(835.00)%20HANA070121%3CBR%20%2F%3E%24(4%2C736.00)%201435712742%20%24(766.50)%201201297-000%20%24(520.00)%20212572-1631%20%24(828.00)%20HAR070121-1563%3CBR%20%2F%3E%24(4%2C002.00)%201446312852%20%24(766.50)%204080605-000%20%24(1%2C049.10)%20214313-1631%20%24(1%2C900.00)%20MUS070121%3CBR%20%2F%3E%24(1%2C136.01)%201473813137%20%24(519.00)%201203484-000%20%24(310.00)%20214401-1631%20%24(1%2C350.00)%20SEV070121%3CBR%20%2F%3E%24(3%2C427.60)%201476013162%20%24(750.00)%20215237%20%24(2%2C060.00)%20HAR080121-1563%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20spreadsheet%20is%20set%20with%20several%20vendors%20all%20next%20to%20each%20other%20and%20I%20realize%20it%20needs%20to%20read%20the%20numbers%20as%20text%20which%20is%20what%20I%20am%20asking%20how%20do%20I%20get%20it%20to%20read%20them%20as%20text%3F%20I%20used%20to%20get%20a%20sort%20warning%20asking%20how%20I%20want%20to%20sort%20and%20this%20would%20sort%20them%20as%20text%20but%20I%20no%20longer%20get%20this%20warning.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2870544%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2870544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3EI%20have%20attached%20a%20sample%20of%20my%20spreadsheet%20as%20it%20is%20too%20large%20to%20attach%20more.%20I%20am%20trying%20to%20sort%20the%20numbers%20next%20to%20the%20dollar%20amounts%20while%20keeping%20the%20dollar%20amounts%20next%20to%20the%20numbers%20so%20columns%20E%20%26amp%3B%20F%20need%20to%20be%20sorted%20together%20I%20only%20want%20to%20sort%20from%20rows%206%20through%2016%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have two columns that are connected and I want to sort by the second column, I want it sorted numerically but it keeps moving the numbers with the -1631 to the bottom of the list how do I fix this?

I have other columns on the same spreadsheet that I sort and I get a sort warning asking to read as numbers and I click this and it sorts fine.

 

Column A has Dollars

Column B has  49900

                        49815-1631

                        49816-1631

 

I want it to read  49815-1631

                            49816-1631

                            49900

                             50020-1631

and I want to be able to sort this while sorting Column A so it stays together. I have multiple columns on this spreadsheet that have two columns that are attached.

 

8 Replies

This is a guess.

 

I don't think Excel can read 49816-1631 as a number. That is to say, it's NOT a number, not until you (or Excel) does the subtraction. In the meantime, it is, by definition, text. Now, maybe it's a zip code (the way it's formatted, that occurs to me as a possibility). Are those other columns you refer to, where you get prompted to sort as numbers, also "numbers" mixed with text in the same field? If they really are just arabic numerals, even if formatted as text, then Excel is smart enough to treat as numbers, but what you have here, with the "-" dividing one set of numerals from another is text and can't be other than that. 

 

As I said, it's a guess, but a semi-informed one. If you'd care to post the spreadsheet, or a portion of it, we can test my hypothesis.

@mathetes Hi. You will have to interpret the part before the minus sign as numeric.

c4 =IFERROR(NUMBERVALUE(LEFT([@Data];FIND("-";[@Data])-1));[@Data])

 

FIND("-";[@Data])-1)

Find the minus and substract one to find the numnur of digits.

 

LEFT([@Data];FIND("-";[@Data])-1))

Get that number of characters

 

NUMBERVALUE(LEFT([@Data];FIND("-";[@Data])-1))

Turn it into a numeric value

 

IFERROR(NUMBERVALUE(LEFT([@Data];FIND("-";[@Data])-1));[@Data])

If there is no minus FIND will generate an error. If so, return the value as is

 

bosinander_1-1634714729748.png

 

Column D and E are optional depending on your need.

d4 =IFERROR(NUMBERVALUE(RIGHT([@Data];LEN([@Data])-FIND("-";[@Data])));0)

e4 =[@[Num 1st]]-[@[Num 2nd]]

 

[@Data] means same row in pyamas tables field Data.
If using an ordinary range, replace [@Data] with B4.

 

Generally, I prefer using MID since it's one function that can be used also for left and right. In the attached file though, LEFT and RIGHT are used.

Here is picture of my spreadsheet. One column is the dollar amount the other column is an invoice number and I need the invoice number in numerical order so I highlight the 2 columns under the vendor to sort by the invoice number.

Northeastern Refrigeration OP Aquatics The Plumbing Source Rinaldi Landscape

Amount Inv. # Amount Inv. # Amount Inv. # Amount Inv #

$(1,940.00) 1377712142 $(631.50) 1197576-000 $(355.00) 212807 $(835.00) HANA070121
$(4,736.00) 1435712742 $(766.50) 1201297-000 $(520.00) 212572-1631 $(828.00) HAR070121-1563
$(4,002.00) 1446312852 $(766.50) 4080605-000 $(1,049.10) 214313-1631 $(1,900.00) MUS070121
$(1,136.01) 1473813137 $(519.00) 1203484-000 $(310.00) 214401-1631 $(1,350.00) SEV070121
$(3,427.60) 1476013162 $(750.00) 215237 $(2,060.00) HAR080121-1563



The spreadsheet is set with several vendors all next to each other and I realize it needs to read the numbers as text which is what I am asking how do I get it to read them as text? I used to get a sort warning asking how I want to sort and this would sort them as text but I no longer get this warning.




@mathetesI have attached a sample of my spreadsheet as it is too large to attach more. I am trying to sort the numbers next to the dollar amounts while keeping the dollar amounts next to the numbers so columns E & F need to be sorted together I only want to sort from rows 6 through 16

Thank you for this response but this won't work for what I am trying to do. I already tried it I need the -1631 to stay with the number without losing my sort

@lxj190 Sort by the numeric column but look at the alphanumeric..?

bosinander_0-1634840453086.png

 

...or turn the column into text format making the numbers treated as strings.

Menu Home: Number: 

bosinander_1-1634840631724.png

 

 

Thank you I did try to turn it into text, but it didn't work on all my columns, I found that if I put an ' in front of the number without the dash it gives me the warning box and I can pick sort as text. So thank you for the help

@lxj190 Right- if the invoice number not always begins numeric, then it will not work as proposed.

Good that the apostrophe (comment character) helps. And yet another possibility may be even more helpful;

=IFERROR(TEXT(F6:F18,"0"),F6:F18)

try to turn the data into text, and if it fails, treat it as already text.

bosinander_0-1634879860813.png

Assuming Excel 365, there is only one formula, spilling the result. If Excel PDA/pre dynamic arrays, use

=IFERROR(TEXT(F6,"0"),F6)

and fill down.