Thread: How do I sort alpha neumeric fields that have an alpha suffix?
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein Rick Rothstein is offline
external usenet poster
  
Posts: 5,934
Default How do I sort alpha neumeric fields that have an alpha suffix?
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.

--
Rick (MVP - Excel)


"Bob Sparks" <Bob wrote in message
...
How do I sort numbers with alpha suffix, e.g. 114, 114A, 114B, etc?

Reply With QuoteReply With Quote