How to Sort Mixed Numbers and Text Using String Functions

Mixed Sort LeadExcel’s built-in sort functions are incredibly useful for organizing data and ordering information like dates, times, and other numerical inputs. Sometimes, though, you may need to sort product IDs, employee numbers, or other information that has letters and numbers in it. Then, what do you do? A normal Excel alphabetical sort will not prioritize the numeric parts of a string on its own. Here is how to sort alphanumeric data with complete control…

 

Looking at the Data

Let’s imagine you have a list of employees. It might have their first name, last name, and job title. All of these are easy to organize around – they are all pure text. Adam comes before Bethany. Smith comes after Johnson. Accountant comes before Technician.

Maybe these employees have an employee number that is based on when they were hired or what division they were recruited from. This might have a different structure like “P<dept_number>-<recruit_date>”. It might make sense to sort the employees by department number first and then by recruit date… Let’s see if Excel is smart enough to do this on it’s own.

Unsorted Data

Trying a Normal Sort

Select the columns of data you want to be affected by the sort. If you are following along with the example, it’s A:D. Then click on the Sort button on the Data tab of the menu.

Data Sort Menu

Choose to sort by the Employee Number field and click OK.

Automatic Sort

Let’s see how well it worked. On first glance, it might look like it did the trick… The first numbers after the “P” are sorted, but wait…

Automatic Sort Result

For some reason, Excel thinks that “P51” comes before “P5”. And “P42-16” is sorted in front of “P42-5”. How is this possible?

Since the cells contain text as well as numbers, Excel treats the entire cell like a text string. It sorts according to the order the “letters” appear instead of the entire number (e.g. the “1” in “P42-16” comes before the “5” in “P42-5”).

We’re going to need to do a bit more work to make Excel do our bidding…

Creating Sort Columns

To get around Excel’s annoying handling of numbers in text, we’re going to create a couple of columns to sort with. They’ll keep the original cell un-changed, but let us re-arrange the data how we’d like.

Select the column to the right of the mixed cell you want to sort (Employee Number in this example). Right-click the column labels and click Insert to add a column.

Insert Column

Press CTRL-Y to repeat the action and add another column.

Finding the Sort Data

We are going to use a combination of functions that look at text data to pull the numbers out of the mixed string (another article uses similar string parsing tactics to change notation). First, we’ll tackle the number after the “P” in the example, using the MID() function. The syntax for MID is as follows:

=MID(text, start_num, num_chars)

The MID function can take part of a string, but we need to tell it where to start and finish. Some of the numbers are single-digit and others are double-digit, so it’s not as easy as looking for the second character. We need to focus on what makes the numbers similar. In this case, they all come after the “P” and end right before a “-” (hyphen).

The start character in this case is always the second (after the “P”), so we can specify it directly, so start_num is 2.

The number of characters in the first number is different each time, so we need to be more sneaky. We’re going to count the number of characters before the hyphen using the FIND() function. The syntax for FIND is as follows:

=FIND(find_text, within_text, [start_num])

In this case, we’re looking for the hyphen, so find_text is “-“. We don’t need start_num this time.

The FIND function is going to report the location of the hyphen in the Employee Number, but we need to tell MID how many characters to pull from the string, so we’ll have to subtract 2 from the FIND result: 1 to get in front of the hyphen and 1 to discount the “P” at the beginning.

The MID function is going to provide the part of the Employee Number that we want, but it’s still going to be text. When we sort, we need it to be a number, or the sort will treat it the same as it did in the first attempt. For that, we wrap it in a VALUE() function. The syntax for VALUE is simple:

=VALUE(text)

The final formula for the first sort cell (B2 in the example) is as follows:

=VALUE(MID(A2,2,FIND(“-“,A2)-2))

The formula is complicated, so rather than type it again, you can drag it down and Excel will automatically change the cells as necessary:

Drag Down Formula Drag Down Formula Result

 

Sorting After the Separator

Now we need to extract the number after the hyphen in the Employee Number. We’re going to use a similar strategy, but we need a couple additional formulas. We could use the MID function, but it would be cumbersome since the cells are different lengths. Instead, the RIGHT() function will pull characters from the end of a string. The syntax for the RIGHT function is as follows:

=RIGHT(text, [num_chars])

The RIGHT function needs to know how many characters we need, so a FIND function isn’t enough. We also need to know the entire length of the string to find out how many characters are after the hyphen. For that, we’ll use the LEN function. The syntax for the LEN function is easy:

=LEN(text)

If we subtract the location of the hyphen from the total length of the Employee Number, we’ll have the exact number of characters needed for the RIGHT function. Finally, we wrap the entire formula in another VALUE function to make sure we get the number instead of the text.

The final formula for the second sort cell (C2 in the example) is as follows:

=VALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))

When you’re sure it works, drag down the formula to fill in the rest of the cells. To make them easier to find, you can title the columns – I called them “EN Sort 1” and “EN Sort 2“.

Sort Columns

Now that the columns are created, we can safely hide them from view, since they aren’t important to reading the data. Just select the two new sort columns, right click the header, and click “Hide” from the menu.

Hide Columns

Sorting with the New Sort Columns

Select the new sort columns (B:C). Then click on the Sort button on the Data tab of the menu.

Data Sort Menu

Choose to sort by the EN Sort 1 column. Click the “Add Level” button to specify a second sort criteria. Choose to sort by the EN Sort 2 column in the second level. Then, click OK.

Using Sort Columns

Examine your data. See how nicely it behaves? Good work!

Alphanumeric Sort Example

Below is the workbook example from the tutorial. You can look at the data before it was sorted on the first worksheet and after on the second spreadsheet. You can also download the sample file by clicking the Excel icon in the bottom right.

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

33 thoughts on “How to Sort Mixed Numbers and Text Using String Functions

  1. Andrew, I noted you did not have any single digits in the employee number column at the first sort column. I cannot tell if your sort would put the single numbers 1 after 10 or 2 after 20. And what if the employee number is alphanumeric without a hyphen?

  2. Hi Chris –
    The formulas in this example separate the numbers from the letters and hyphens and sort them numerically. This means that 1 would be sorted as less than 10, 2 less than 20, etc. If your employee number had a different format, the RIGHT, LEFT, and MID functions would need to be modified to capture the numbers in that new format.

  3. hello sir,
    I have a text line in excel having numbers and text I want to extract only specific number from that line having 6 digit line is given below “” -1130- -INTT- -701000-100-EXP- – – Operations-International-Company Labor-General Plant-Expense”” from this line I want to take only “”701000″” into next cell.

    please note number are different but digit are same.

    thank you very much.

  4. Sir, I want to pull a value from one sheet to another sheet in same workbook. This is quite easy by putting the formula of “=”, but when I want to drag, its pulling one after another cell from that sheet but I need next to next cell. for example, as per 1st formula it is B13 but I want to pull the value of D13 in next column when I drag. So what type of formula I have to put. Please suggest.

    I have attached the file. Main sheet is the sheet where to pull the formula from any other difference.

    1. I would probably use OFFSET with COLUMN(). Say you want data from A1:G7 on the Data_sheet, and you are typing in Final_sheet in A1, you could use the following formula on Final_sheet in A1 and drag it down and across.

      =OFFSET([Data_sheet]Sheet1!$A1, 0, COLUMN()*2-2)

      What this does is it checks which column you are in on Final_sheet (so 1 for A, 2 for B, 3 for C) and multiplies it by 2, then moves that many cells right. This is why you get every other result. The fact it is only absolute in one dimension (i.e. it is $A1 instead of $A$1) means it will move down one row at a time. If you want it to move two at a time you can do a similar thing with ROW()*2-2.

      An alternative is just to use = and then hide the other columns…

  5. I’m trying to count all the text leading up to the first number in the cell.

    here is an example of what it looks like.

    Gloves Latex Medium 10/100 Count 20lb 1c

    can someone help me with a formula.

    Thanks

    1. Locate the location of the first digit. =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))
      The result minus 1, gives you the lenght of the string, including the spaces.

      If you want characters only you have to add a few more formula’s:
      To extract the string: =LEFT(A2,B2-1)
      To count the spaces in the string: =LEN(C2)-LEN(SUBSTITUTE(C2,” “,””))
      To calculate the number of characters minus the spaces: =B2-1-D2

      Note: there are various add-on tools for Excel to do such actions within just a few mouse clicks.

  6. Hello. I am an Excel newbie, and I need to sort mixed numbers, letters, And symbols. Is there a formula that can sort varied items like in this list…

    PLC/A-M6-1
    PLC/B-M6-1
    LL-6
    MM-6
    LL-61
    MM-61
    PLC/A-M6-15
    PLC.B-M6-15
    PLC/A-M5-1
    PLC/B-M5-1
    MM-5
    LL-7

    into the order shown in this list below… I’m not even sure How to describe what I need here, other than to just show it! Sorry! And Thank You for your time.

    LL-6
    LL-7
    LL-61
    MM-5
    MM-6
    PLC/A-M5-1
    PLC/A-M6-1
    PLC/A-M6-15
    PLC/B-M5-1
    PLC/B-M6-1
    PLC/B-M6-15

    1. Hello,

      If the first cell is A1, then in the next column insert: =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789″)),
      in the second =LEFT(A1,(B1-1)),
      in the third =(MID(A1,B1,3)),
      after then sort by second column then by third column.

  7. Need help in formula for below mentioned query

    I have a set of name for e.g. (Mahesh\\Santosh\\sanjeevani\\Umesh) this all is in one row, now i need to break up in such a way that if column B1 =1 then it should be Mahesh, if B1=2 then it should be Santosh, if B1=3 then it should be Sanjeevani, if B1=4 then it should be Umesh.

    Please help me in this formula to solve my issue.

    Thanks & Regards,

    ST

  8. need help with below please….in excel there is a string of chart that I need to convert to another chart of account but need to insert separator in string to be able to translate to another chart…your help please :
    11010006023050000
    11010006023060000
    11010006023070000
    110100-6023080000
    11010006023090000
    11010006023100000
    11010006023110000
    11010006023120000
    to be able to translate I need data in this format:
    1.101.000.60.231200.00

    1. Try this

      =LEFT(A11,1)&”.”&MID(A11,2,3)&”.”&MID(A11,5,3)&”.”&MID(A11,8,2)&”.”&MID(A11,10,6)&”.”&RIGHT(A11,2)

      1. Of course you’ll substitute your cell in place of my A11 test cell, but the formula should get you the format you’ll need.

  9. HI Need Help, on the below,

    I have Cell A1 = HT-1-2017, how do i extracted the “1” from this cell.

    I followed your above sample and tried this:
    =VALUE(RIGHT(A1,LEN(A1)-FIND(“-“,A1))) and this is the output: 42736

    Since there are two hyphens the above command doesn’t work, but if i have only one hyphen then result is ok.

    1. You need to locate the 2nd hyphen, using FIND(find_text, within_text, start_num), by setting the start_num to the location of the 1st hyphen plus 1.

      Answer =MID(A1, FIND(“-“,A1)+1, FIND(“-“, A1, FIND(“-“,A1)+1) – FIND(“-“,A1)-1)

      Here’s a more understandable version using B1 to store the 1st hyphen plus 1:
      Cell A1 =”HT-1-2017”
      Cell B1 =FIND(“-“,A1)+1
      Cell C1 =MID(A1,B1,FIND(“-“,A1,B1)-B1)

  10. Hi, my problem is sorting product serial numbers that contain letters and numbers. I would like to sort them by the number. However, I do not make the serial numbers so they can vary a lot. Some might be simple like wb10, sh5 but others could be a27a1, tga39 etc. Because there is no structure to the serial numbers is it possible to sort them by the number?

  11. Hi, I am trying to sort VIN numbers by even odd. It seems there must be a way, using just the last digit to sort to true and false, but the letters within make it an error. Seems this method may work, I’m just uncertain how to write it up. VINs have 18 digits total

  12. Hi

    I am half way there. My number example is 6531-A. When I apply the formula for the first sort column it returns the “531” and drops the 6. I presume this is because your formula excludes the P and therefore my 6. How can I get it to pick up all four numbers for the first sort column?
    At the moment our number run goes 6531 and 6531-A. Do I need to put an A, B etc behind each number for this to work?

  13. I need to sort a column with numbers and letters, from small to big. The first two characters are the same through out. But the numbers go from 4 to 6 characters long.
    reg sort eg)

    AX3298
    ax32954
    ax3296

    There are 74 rows.

  14. Hi,

    I am trying to sort A01, B01, C01, D01, E01, F01, G01, H01 which is repeated until H12. A01-H08 are sorted properly but not A09-H12. Can anybody help me with this?

  15. Hello i have been searching for a solution for my dilemma. I have a long list of numbers 1600rows, values 2000-3000. I would like to convert to 16 columns of 100 rows with similar value totals

  16. Hi, I was wondering How can I sort this:

    zz-015
    zz015

    I want to have the – (dash part) to be sorted in front.
    currently when I sort it, it does zz015 first

  17. I would like to sort data that looks like this:

    1
    2
    3A
    3B
    4
    5
    etc. Alternatively, I tried using 3.1 and 3.2 instead of throwing letters in there but that did not work either. Can you help with this?

    1. Actually, nevermind. the .1 and .2 did work. But if you have a solution for the A, B, issue, that would be great, too!
      Thank you 🙂

  18. HI, i am a total newbie at excel, so, i am so frustrated that I can’t see straight. I am thrilled to get the E- numbers to sort in order using this formula C2=VALUE(RIGHT(B2,LEN(B2)-FIND(“-“),A2))). I am searching for a miracle formula that will put the number is numerical order: 1 2 3 4 5 (instead of 1,10, 100

  19. I have a spreadsheet with many state codes (AL, AZ, FL, etc) and people names beside each. Then I have another spreadsheet from which I have used the MID function to extract the state code from a cell with Street name and number, state and zip. The two are then merged so that I have a long list of people names and states in separate columns. I want the list sorted by state and by name (secondarily). But when I sort, I gel all of entered states with AL with the names sorted alphabetically and then the extracted states follow for AL, also sorted by people names. I want one list for AL, not two.

  20. Hai I want to sort out a row into Colin like this to that…
    Kos – 0123, Kos – 0234, Kos – 456, Kos – 0124, Kos – 0125…..in a row
    Can you please help me to sort like this given below in a column????

    Kos – 0123
    Kos – 0124
    Kos – 0125
    Kos – 0234
    Kos – 0456

  21. Any idea how to sort varied lengths on the alphanumeric stuff? I have data that goes like this:
    1
    3A
    5

    17
    17A
    17B
    22

    100
    114
    114A

    And so on. I can’t change them to have trailing numbers or something like that, they’re designations for official documents already filed, I’m just organizing them.

Leave a Reply

Your email address will not be published. Required fields are marked *