## Sorting numbers and text cells also removing blanks using an array formula

*Article updated on August 21, 2017*

**Question:** How do I sort text and numbers and also removing blanks using an array formula?

**Answer:**

**Array formula in B2:B15:**

Recommended articles:

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

**How to enter an array formula**

- Copy array formula (Ctrl + c)
- Select cell B2
- Click in formula bar

- Paste formula (Ctrl + v)
- Press and hold CTRL + SHIFT
- Press Enter

If you did this correctly, the array formula now begins with a { and ends with a }.

**How to copy array formula**

- Select cell B2
- Copy cell B2 (Ctrl + C)
- Select cell range B3:B14
- Paste (Ctrl + v)

**Download excel example fil****e**sort-numbers-and-text-cells-using-excel-array-formulav2.xls

(Excel 97-2003 Workbook *.xls)

**Functions used in this blog post:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IF(**logical_test, [value_if:true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array, row_num, [column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array,k**)
**Returns the k-th smallest number in this data set.

**ROW(**reference**)**

Returns the rownumber of a reference

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

*This blog article is one out of six articles on the same subject.*

- Sorting text cells using array formula in excel

- Sorting numbers and text cells also removing blanks using array formula in excel
- Sorting numbers and text cells descending also removing blanks using array formula in excel
- Sort text cells alphabetically from two columns using excel array formula
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Sort a range from A to Z using array formula in excel

Read more related articles

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 26 Responses to “Sorting numbers and text cells also removing blanks using an array formula”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Okay here's what I need to do:

I have 5 lists each with somewhere between 10 to 30 text values associated with a title of that list. On another worksheet I have 2 dropdown lists with the 5 list names in each one. You may choose one of the names or two. I want another worksheet (based on those choices) to return all matching results and place them in a dropdown list, I need it to update if you change the choices at any time.

Does anyone know how to do this? I've been able to figure out alot of things but this one I can't clearly find out how to accomplish. I can and have used VBA on this sheet to accomplish other things but I'm not proficient in writing the code from scratch as I'm still learning it.

I don't see in your page my problem. I have a matrix 5x100 I tried wiht data-sort..etc. I need to sort by row, nothing can be change by columnns, so data-sort change one row, when I tried the next, the first one come back to the original desorger, how can I change the whole matrix in one time by rows not by columns. example.

9-8-7-6-5

6-5-3-2-1

98-87-54-21-32 etc.

thank you for any help.

baum schausberger,

Excel 2007:

1. Select cell range

2. Right click on cell range

3. Click Sort...

4. Click Custom sort

5. Click options...

6. Select Orientation: Left to right

7. Click OK

8. Select a row

9. Click OK

This doesn't work correctly with text starting with an equal sign:

(Yes, I know it's a pathological case)

1) Enter some normal data in the List range

2) Enter a text string starting with a single quote and an equal sign (or, format a cell as Text and put a formula in it)

3) notice duplicate entries in the output not present in the input

List Sorted

a 1

aaa 2

1 9

2 10

b 11

777 777

9 1E+88

c a

10 aaa

11 aaa

1.00E+88 b

=A17+1 c

e e

g f

f g

here's the same data, but easier to read:

List

a

aaa

1

2

b

777

9

c

10

11

1.00E+88

=A17+1

e

g

f

Sorted

1

2

9

10

11

777

1E+88

a

aaa

aaa

b

c

e

f

g

Note the duplication of the aaa entry, and the non-inclusion of the text entry starting with =

Also, the formula failed to work with the entire text of the United States Declaration of Independence in a single input cell. It just puts #VALUE in every output :-)

and for shorter multi-line inputs, it concatenates them:

'ab'

becomes

'ab'

although 'ab' becomes 'ab', it's still sorted differently. The comment form isn't allowing me to correctly format what I'm trying to write.

Brian Minton,

This is what I get:

Hi Osacar,

Thanks for sharing this woderful formulas.

Is there anyway we can avoid the "#NUM!" and get black instead of that?

Manu,

=IFERROR(INDEX($A$2:$A$15, MATCH(SMALL(IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), ROW(A1)), IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), 0)),"")

Is there a limit to the number of cells this will work for? I'm using excel 2003 and there seems to be a limit of 25 cells for sorting. Anything more and it starts to add duplicates.

I have an array sheet that pulls 50 cells from a data dump based on the date. I'm using an "INDEX-SMALL-ROW" array function to pull based on the date, but the data is not sorted. The number of entries changes every day, but is less than 50. Then I use the above formula to sort the data for output to my report page.

Is there any way to extend this formula to be applied to more cells?

Buffalo Bill,

This is an old article, I made a new formula and uploaded a new file.

I am sure the old formula could handle more than 25 cells, it really depends on your computer hardware.

If you can´t get it working, upload your workbook and I´ll see what I can do.

This, along with the related formulas, is pure genius! I tested it on sample data, and I am sure I can put this to use in the spreadsheet I am working on.

Thank you!

~ Jere

J Becker,

thank you!

how to sort if there are dates? Lets say in format: 14.03.1999

Your old formula removes duplicates, this one seems to not.

thanks

Fantastic site.

I am using Excel 2013 and the formula worked great except for one small problem. I am using a spread of 150 cells (to allow for the list to grow) It sorted my data as shown:

#180

#181

#250

#275

1462

1468

1469

1471

...

The problem I am having is that it stacked a hundred blank cells above it rather than ignoring them like I would prefer. What am I missing?

[…] PS. For a background, I ran into this problem when I tried to build a formula that would both extract unique values from a list with possible duplicates, and sort the unique values in numerical/alphabetical order. My current solution is to do this in two steps. One solution for how to do it in one step is proposed here. […]

Hi, Is there any way to extend the above sorting formula for cell range A2 to A200 or more. need it urgently please

Have you tried changing the A$15 to A$200?

=INDEX($A$2:$A$200, MATCH(SMALL(IF(ISBLANK($A$2:$A$200), "", IF(ISNUMBER($A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200)+SUM(IF(ISNUMBER($A$2:$A$200), 1, 0))+1)), ROW(A1)), IF(ISBLANK($A$2:$A$200), "", IF(ISNUMBER($A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200), COUNTIF($A$2:$A$200, "<"&$A$2:$A$200)+SUM(IF(ISNUMBER($A$2:$A$200), 1, 0))+1)), 0))

Yes, I tried. but its not taking. the cells are becoming blank otherwise showing N/A

Hi, Now its working but the empty cells are showing first and then the sorted data. But i dont want the cells which are blank between A1 to A200. I want the data to be displayed from column A2 itself. Please help

Great routine. I'm working on a template for our local genealogical and historical society. I'm trying to enter and sort regiments, such as 78 PA, 5 USCT, 14 PaC, etc. I've yet to figure out how to sort this data and have it in the correct order. I've tried eliminating the space between the numbers and letters and even put the letters before the numbers. No joy. Any thoughts on what additional measures I might consider?

B.

B.

Have you read the instructions here?

https://support.microsoft.com/en-us/help/322067/how-to-correctly-sort-alphanumeric-data-in-excel

Hi Oscar,

I have found myself in a bit of a sticky situation.

I get information in a csv format. None the less, I need to somehow be able to access this information via charts/graphs by date range and not sure where to start. Please can you guide me on the correct path.

-Challenges: There is multiple information for the same date, and for the same hour. All I need is either the average of each hours info in a chart over 24 hours, or just the first reading for each hour.

I have broken down the info into the following columns:

A1=Date B1=Time C=pH D=Pressure E=MV F=MV2 G=uS/cm

4/22/2018 16:00:02 6.26 1.80 874 881 1316

4/22/2018 16:01:02 6.26 1.80 874 881 1317

4/22/2018 16:02:02 6.26 1.80 874 881 1318

4/22/2018 16:03:02 6.27 1.80 874 881 1318

4/22/2018 16:04:02 6.27 1.80 874 880 1318

4/22/2018 16:05:02 6.28 1.80 874 880 1317

4/22/2018 16:06:02 6.28 1.80 874 880 1317

Just a copy of the typical data.

Please let me know if you can assist.

Kind regards

Dylan

Dylan,

Array formula in cell B18: