Forum Discussion

Copper Contributor

Oct 19, 2021

Alphanumeric sorting

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

excel

bosinander

Iron Contributor

Oct 20, 2021

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

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.

lxj190

Copper Contributor

Oct 21, 2021

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.