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
Filter records containing a valuev3
Filter records category
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Lookup with criteria and return records.
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Excel categories
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.
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