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

How do I sort numbers with alpha suffix, e.g. 114, 114A, 114B, etc?

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

Have you tried sorting? I get 114 first then 114 with the a,b,c, etc in
order. What do you need?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

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?

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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Bob Sparks" wrote:

How do I sort numbers with alpha suffix, e.g. 114, 114A, 114B, etc?