## How to extract rows containing digits [Formula]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9.

**What's on this page**

- Question
- Filter rows containing at least one digit in any cell on the same row (Array formula)
- Filter rows containing at least one digit in any cell on the same row (Excel 365 formula)
- Filter rows containing at least one digit in any cell on the same row (Formula and an Excel Table)
- Get the Excel File here

Hello Oscar,

What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

**Answer:**

The data set above contains random characters, some of the cells in column B contain numeric values, as well.

## 2. Filter rows containing at least one digit in any cell on the same row

**Array formula in cell F2:**

### 2.1 How to enter an array formula

- Copy formula above
- Doublepress with left mouse button on cell F2
- Paste formula
- Press and hold CTRL + SHIFT
- Press Enter

If you did this correctly, the formula in the formula bar now begins with a curly bracket and ends with a curly bracket, like this: {=formula}

Don't enter these curly brackets yourself, they will appear if you did the above steps.

Copy cell F2 and paste to cell range F2:I6.

### 2.2 Explaining array formula in cell F2

#### Step 1 - Look for values in a cell range

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6)

becomes

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, {"ab12"; "abc"; "def"; "a21b"; "cde"})

and returns this array:

{3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}

#### Step 2 - Remove errors

IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0)

becomes

IFERROR({3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}, 0)

and returns

{3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

#### Step 3 - Return the matrix product of two arrays

MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, ROW($A$1:$A$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1;2;3;4;5;6;7;8;9;10})

and returns

{11;0;0;7;0}

#### Step 4 - Check whether a condition is met

IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), "")

becomes

IF({11;0;0;7;0}, MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), "")

becomes

IF({11;0;0;7;0}, {1;2;3;4;5}, "")

and returns {1;"";"";4;""}

#### Step 5 - Return the k-th smallest value in array

SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROWS($A$1:A1))

becomes

SMALL({1;"";"";4;""}, ROWS($A$1:A1))

becomes

SMALL({1;"";"";4;""}, 1)

and returns 1.

#### Step 6 - Return a value of the cell at the intersection of a particular row and column

=INDEX($B$2:$E$6, SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

=INDEX($B$2:$E$6, 1, COLUMNS($A$1:A1))

becomes

=INDEX($B$2:$E$6, 1, 1)

becomes

=INDEX({"ab12", "PEN", "YPT", "KVF"; "abc", "ZLZ", "KIK", "HQX"; "def", "CJI", "YMI", "STC"; "a21b", "TQW", "XHA", "UBM"; "cde", "YZX", "GLT", "TED"}, 1, 1)

and returns ab12 in cell F2.

## 3. Filter rows containing at least one digit in any cell on the same row (Excel 365 formula)

Excel 365 formula in cell B8:

## 4. Filter rows containing at least one digit in any cell on the same row (Formula and an Excel Table)

If you rather want to use an excel table filter, follow these instructions

- Select data set, cell range B2:E6
- Go to tab "Insert" on the ribbon
- Press with left mouse button on "Table" button or press CTRL + T

- Press with left mouse button on OK
- Double press with left mouse button on cell F2
- Type: =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B3))>0
- Press Enter

- Press with mouse on black arrow on Column 5 (F)

- Filter "True"
- Press with left mouse button on OK

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

### 12 Responses to “How to extract rows containing digits [Formula]”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hello Oscar

I place lots of pictures in word 2016 files. As a person reads the report, I would like a button in the line to open a picture. The picture would relate to the text that was just read. Like you did with Excel (Show / Hide). It would be great if I could change the pictures with ease. Could you write this program? What would be the cost?

John

Dear Oscar,

I Need solution to get summary of days on which the specific leaves have been taken by an employee. Suppose on Ist and 2nd day he availed two casual leaves so summary of days should be come up like 1,2(days) in same row and same applicable for Present days, Festival Holidays.

1 2 3 4 5 6 7 CL FH P

CL CL P P P NH NH 1,2 14,15,16 3,4,5,

Would be grateful to you.

Warm Regards

SACHIN

Hello Oscar, Honestly you are one of the most skilful, intelligent people in excel/vba field. You have unique way of teaching others and communicating knowledge. Simply you awesome. Thank you very much. I am regular reader of your site and I find it very useful learning resource.

Abbasabdulla,

Thank you.

dear Oscar, many thanks for your support & efforts

i need your help,

i have excel sheet as follow

AAA 18-Jan-17

AAA 19-Jan-17

AAA 20-Jan-17

AAA 18-Mar-17

AAA 22-Jan-17

AAA 23-Jan-17

BBB 24-Jan-17

BBB 25-Jan-17

BBB 18-Mar-17

BBB 27-Jan-17

BBB 28-Jan-17

BBB 29-Jan-17

BBB 30-Jan-17

CCC 31-Jan-17

CCC 1-Feb-17

CCC 2-Feb-17

CCC 18-Mar-17

CCC 4-Feb-17

CCC 5-Feb-17

column A has over than 30000 rows consist of duplicate values and in column B the date corresponding to this value.

i wish to create conditional format formula to identify the most recent date only.

in the above example i wish to highlite only the date of 18-March-17.

hope to hear from you soon

kind regards

M. Saad

Thank you, I hope this can be useful.

dear Mr. Oscar , many thanks for your prompt action & answer, apologize that my question was not clear enough for you.

the formula is working very good but i wish to check the recent date for each value in column A

i want formula to check all dates related to cell contain value A and then choose the most recent date , and then check all dated related to cell contain value B and then choose the most recent value and after that go for all dates related to cell contain value C and choose most recent date and etc...

sorry if i waste your time in first question , but actually i want to create link between column A which contain values and column B which contain dates.

sorry again and hope my question is clear this time

kind regards

M. Saad

I believe you are looking for this:

Dear Mr. Oscar

I do appreciate your effort , this exactly what I need for my file.

Again, many thanks and wish you very nice day

Kind regards

M. Saad

You are welcome.

Hi Oscar,

below is my query,

1)11kV Class 3x240 Sqmm XLPE UG Cable (Round Armoured)

2)Straight Through Jointing Kits HS Type suitable for 3x240 Sqmm Cable

3)Cable termination kit Outdoor/Indoor HS Type Suitable for 3x240 Sqmm Cable

4)11kV, 200Amps Single Break GOS

5)H - Frame Set for 11kV, 200Amps Single Break GOS Mounting - MS

6)Earthing materials pipe type for grounding as per Drawing No. BESCOM/GM/CP/15 & 34/

7)45kN Polymeric Disc insulators

8)9 Mtr PSCC DP Structure

From above list i need to create sepaerate column which should display the row containing only 3x240

Oscar:

Sinceramente es un placer leerte. Muchas gracias por ayudar y compartir tus conocimientos.

Necesito de tu ayuda: Tengo un rango determinado de personas (23) en una columna cada una identificada con su nombre. En la columna siguiente el peso (kg) de cada una de esas personas. Preciso clasificar a estas 23 personas por su peso y por lo tanto necesito una regla o formula que me permita agrupar a estas personas en grupos en donde no superen los 5kg de diferencia entre unos y otros. Los que queden fuera de la regla, no podrán competir en esa categoría y los que estén dentro de ese rango (5Kg entre unos y otros) podrán competir entre si.

Nuevamente gracias por tu tiempo

Saludos

Lucas