# How to Sort Mixed Numbers and Text Using String Functions

Excel’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.

## 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.

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

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…

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.

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:

## 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“.

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.

## 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.

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.

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 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!

## 40 thoughts on “How to Sort Mixed Numbers and Text Using String Functions”

1. Chris says:

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. jahangir says:

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.

1. JACKIE says:

How to sort this combine text & number from lowest to highest (1-118)

DLNR inventory update 1
DLNR inventory update 10
DLNR inventory update 100
DLNR inventory update 101
DLNR inventory update 102
DLNR inventory update 103
DLNR inventory update 104
DLNR inventory update 105
DLNR inventory update 106
DLNR inventory update 107
DLNR inventory update 108
DLNR inventory update 109
DLNR inventory update 11
DLNR inventory update 110
DLNR inventory update 111
DLNR inventory update 112
DLNR inventory update 113
DLNR inventory update 114
DLNR inventory update 115
DLNR inventory update 116
DLNR inventory update 117
DLNR inventory update 118

4. ASHFAQ says:

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. Aidan says:

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. Shane says:

Can you sort a column by two commands letters first and the numbers? FOr example A1,A2,B1,B2,B3,C1,C2

6. Desmond says:

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. David says:

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.

7. Hey andrew. Sorting the alphanumeric data in a correct way. That was really a nice trick to apply. Thank you

8. Kellie R says:

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. Gabor Szebenyi says:

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.

9. santosh says:

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.

Thanks & Regards,

ST

10. RB says:

Exactly what I needed! Thanks so much!

11. fiti levi says:

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. Neil says:

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. Neil says:

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.

12. Dileep says:

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. Nigelt says:

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)

13. Phil Acton says:

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?

1. Marc says:

can anybody assist with this particular instance I am having the same issue here as far as sorting issues go

14. Erma says:

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

15. Denise Darwent says:

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?

16. Gilligan says:

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.

17. kris says:

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?

18. Dominic Irizarry says:

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

19. Yakaka B M says:

please hiw do I sort number combinations of three and four digits serially?

20. Wilson says:

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

21. Lorena says:

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. Lorena says:

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 🙂

22. valerie says:

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

23. Leo Gillman says:

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.

24. Reichel Robertson says:

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

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

25. Haruspex says:

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.

26. Merle says:

I need to find serial numbers that ALWAYS start with 6 random letters followed by 6 DIGITS. Occasionally those 12 characters combined will also have two additional letters at the end.
Examples:
“ASDFGH123456” and occasionally “ASDFGH123456AB”
These are always found in cells with other text in no particular order or location, if that makes sense.
I know I’m not the only one faced with this problem but I’m finding no answers.

27. Fawn says:

I need help sorting with various numbers and symbols. Following is a small sample of what I am trying to sort.
2614-10/2613-10/2615-10/2616-1
0008-06
100-02
1004-04
1005-07
1006-04
1007-04
1008-04
1009-07
1010-07
101-02

28. Fawn says:

Sorry…this would be a bit easier to understand to my previous post.

2614-10/2613-10/2615-10/2616-1
0008-06
100-02
1004-04
1005-07
1006-04
1007-04
1008-04
1009-07
1010-07
101-02

29. Nita says:

How can I add only numbers from for example
2
2B
4L
0
W
So total should be 2+2+4+0 = 8

How would you sort the following AlphaNumericRows?

For the list of values the standard sort in Excel returns:
1.1
10.1
10.2
1.1.1
1.1.1.1
1.1.1.a
1.1.1.b
1.1.1.c
10.2.1
10.2.2
10.2.3
10.2.4
10.2.5.a
10.2.5.b
2.1.a
2.1.b
2.1.c
2.2.1.a
2.2.1.b
2.2.2.a

The sort order should return as:
1.1
1.1.1
1.1.1.1
1.1.1.a
1.1.1.b
1.1.1.c
2.1.a
2.1.b
2.1.c
2.2.1.a
2.2.1.b
2.2.2.a
10.1
10.2
10.2.1
10.2.2
10.2.3
10.2.4
10.2.5.a
10.2.5.b

31. Y. Ann says:

I have a column with addresses that appear as text; example 102 Sweet Grass, 301 Sweet Grass, 203 Tungsten, 301 Tungsten, etc. I want to sort the addresses so that the numeric value of the address appears sequentially for the same street name in alphabetical order. A simple sort runs numeric first then alpha so I am not able to get all the addresses on a given street. I am not that great on functions within excel and could use help.