General discussion

Locked

Excel: Mix Data Sort

By gmihajlo ·
I need to know how to sort large amount of data in columns when data strings start with one or two characters followed by one to four numbers?
For example Excel gives me : AB100, AB11, AB111, AB2,?
I would like to get: AB2, AB11, AB100, AB111,?and so on.

Can any one help me with a solution?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

Collapse -

by dryflies In reply to Excel: Mix Data Sort

The problem is that when you add the two leading characters, excel believes the contents are a character string and sorts them as ascii. you can't have characters and sort numerically. The good news is that you can split your cells so that column has the characters and column b has the numbers. set column c to concatenate columns a and b. sort by column a, then by column b. column c will have the string in the order you are looking for. if you do not wish to have columns a and b as part fo your output, hide them before printing. If your input comes in the form of AB100, AB2... then you can use string functions LEFT and MID to split them out before sorting. you will have to make sure that you explicitly format the numerical column as a number

Collapse -

by dryflies In reply to

Hi,

Please make sure you rate my answer.

Collapse -

by gmihajlo In reply to

Poster rated this answer.

Collapse -

by SimY In reply to Excel: Mix Data Sort

In addition to dryflies' answer, you can split the column using 'text to columns' under the data menu and inserting a break line between the 'AB' and the numbers.

Collapse -

by gmihajlo In reply to

Poster rated this answer.

Collapse -

by gmihajlo In reply to Excel: Mix Data Sort

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums