## Extract numbers and text from a range sorted from A to Z

*Article last updated on March 14, 2018*

This blog post describes how to filter text values and then numbers from a range.

**Array formula in B15:**

copied down as far as needed.

**Array formula in D15:**

copied down as far as needed.

**Named ranges**

tbl (B5:F11)

What is named ranges?

**How to customize the formula to your excel spreadsheet**

Change the named range. If your list starts at F3, change B14:$B$14 or $D$14:D14 in the above array formulas to $F$2:F2.

Download excel sample file for this tutorial.

Extract numbers or text from a range.xls

(Excel 97-2003 Workbook *.xls)

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

Create a list of all numbers or text in a column in excel

**Functions in this article:**

**MATCH(**lookup_value;lookup_array; [match_type]

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

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

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

**ROW(**reference**)** returns the rownumber of a reference

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

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

**COLUMN(**reference**)** Returns the column number of a reference

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

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

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

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

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

### One Response to “Extract numbers and text from a range sorted from A to Z”

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

Hello, can you please tell me which tbl. In which order you type in the formular.

In your examble is written the same"tbl" but not table "a" and "b"