[Bob Sparks]

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

[Bernard Liengme[_3_]]

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?

[Rick Rothstein]

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?

[Shane Devenshire[_2_]]

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?