## Filter numbers or text in a column

*Article updated on February 06, 2018*

**Question**: I want to extract all numeric values into a new column? Also all text values into another column?

**Answer**:

**Filter numbers into a new column**

Array formula in C2:

=INDEX(List, SMALL(IF(ISNUMBER(List), ROW(List)-MIN(ROW(List))+1, ""), ROWS(C1:$C$1))) + CTRL + SHIFT + ENTER copied down as far as necessary.

**Filter text into a new column**

Array formula in D2:

=INDEX(List, SMALL(IF(ISTEXT(List), ROW(List)-MIN(ROW(List))+1, ""), ROWS(D1:$D$1))) + CTRL + SHIFT + ENTER copied down as far as necessary.

**Named range**

List (A2:A11)

What is named ranges?

**How to implement array formula to your workbook**

Change the named range. If your list starts at, for example, F3. Change C1:$C$1 (or D1:$D$1) in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

List all numbers in a column

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**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 row number in this data set.

**ISTEXT(**value**)
**Checks whether a value is text, and returns TRUE or FALSE

**ISNUMBER(**value)

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROWS(**array**)** returns the number of rows in a reference or an array

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]### 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

## Share this article