Forum Discussion
Alphanumeric sorting
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 Sort by the numeric column but look at the alphanumeric..?
...or turn the column into text format making the numbers treated as strings.
Menu Home: Number:
- 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.
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.