Alphanumeric characters put in numeric sequence

Anonymous
2017-05-17T13:07:53+00:00

I have a spreadsheet that I have alphanumeric sequencing.  I would like to have the sequence to be sorted by numeric value.  The alphanumeric sequence however starts with alpha-numeric - ex. AP135 

I have reviewed a number of documents and I have not come across a situation where it identifies this type of sequence.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-05-17T14:36:51+00:00

    The helper column you're using must be next to the column of original data and it doesn't matter which side it is. You then select both columns and do the sort. You can delete or; in my opinion, hide the helper column if you don't want it to show on your worksheet.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-05-17T14:24:58+00:00

    Hi Mike - process worked as far as separating the alpha characters from the numeric but now can you explain how to bring the 2 columns together and have them in proper numeric sequence.  I have attempted numerous ways and have not yet found a way.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-05-17T13:45:12+00:00

    Hi,

    You can extract the number to a helper column and then sort the 2 columns on the newly created helper column. See the snip below.

    The formula in B2 is this

    =--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))

    Was this answer helpful?

    0 comments