How do I sort alpha neumeric fields that have an alpha suffix?

  • Thread starter Thread starter Bob Sparks
  • Start date Start date
Have you tried sorting? I get 114 first then 114 with the a,b,c, etc in
order. What do you need?
best wishes
 
I would use two helper columns. Assuming your numbers are in Column A
(starting in Row 2), and assuming you use Column P and Column Q as your
helper columns, put these formulas in the indicated cells and copy down to
the end of your data in Column a and then sort all your data referencing
Columns P and Q as the sorting columns...

P2: =LOOKUP(9.9E+307,--LEFT(A2,ROW($1:$99)))

Q2: =MID(A1,TRIM(RIGHT(SUBSTITUTE(RIGHT(P2),"/",REPT(" ",99)),99))+1,99)

Note: Notice that the formula in Column Q refers to values in Column P.
 
Hi,

That depends on how you want them sorted. Please give us more data and tell
us how your result is sorting and how you want it to sort.
 
Back
Top