Sorting goes E100,E1001,E101 not E100,E101 etc. Why???

M

MEANLEANDEANE

Even trying Text,Numbers,General in the Format is giving me the same results.
ie E110,E1101------E1109 THEN E111 E1111 etc
It appears that the numbers are being treated in some type of formula.Could
it be that the combination of alpha and numeric is causing a peoblem?
 
D

David Biddulph

To me they don't look like numbers, they look like text strings, so Excel
will sort them alphanumerically.

If you've always got the one prefix letter, you may be able to get away with
splitting the number part into a helper column, and sorting by that; Excel
kindly gives you the option that anything that looks like a number is sorted
as a number.
 
S

Stefi

E110,E1101------E1109 THEN E111 E1111 are strings for XL.

If you want to sort by numbers represented by characters after E then use a
helper column filled with formula =VALUE(MID(A2,2,256)) (supposed that your
data are in column A) and sort by this helper column!

Regards,
Stefi


„MEANLEANDEANE†ezt írta:
 
M

MEANLEANDEANE

BINGO. Thanks a lot.

David Biddulph said:
To me they don't look like numbers, they look like text strings, so Excel
will sort them alphanumerically.

If you've always got the one prefix letter, you may be able to get away with
splitting the number part into a helper column, and sorting by that; Excel
kindly gives you the option that anything that looks like a number is sorted
as a number.
Click to expand...
 
M

MEANLEANDEANE

Thanlks a lot.

Stefi said:
E110,E1101------E1109 THEN E111 E1111 are strings for XL.

If you want to sort by numbers represented by characters after E then use a
helper column filled with formula =VALUE(MID(A2,2,256)) (supposed that your
data are in column A) and sort by this helper column!

Regards,
Stefi


„MEANLEANDEANE†ezt írta:
Click to expand...
 
S

Stefi

You are welcome! Thanks for the feedback!
Stefi


„MEANLEANDEANE†ezt írta:
 
Top