## Filter numbers or text in a column

*Article last 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

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 […]

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 […]

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 […]

Sum values containing text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

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), […]

Return value if it exists more then n times

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for […]

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form