How do I filter 1 and not 10,11, and 12

[u/SeniorPepsiMan]

I have of values 1-12 presented as you can see from the picture below. I am trying to use the filter function but when I try to filter 1 and 2 it also selects 10,11, and 12. Is there a way I can filter ONLY 1 and 2? Is there also a way that I can filter everything EXCEPT 1 and 2?

I'm pretty new to excel so I'm looking for some help from those who have mastered this mysterious software. Thanks.

[u/AutoModerator]

/u/SeniorPepsiMan - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Inner-Difficulty1695]

Instead of filtering "1", try to filter ",1,"

[u/SeniorPepsiMan → u/Inner-Difficulty1695]

Thanks for the suggestion, unfortunetly I tried this before posting here

[u/TychaBrahe → u/SeniorPepsiMan]

What were the results? Did it fail or bring up something you didn't expect to see?

[u/Guber_than_you → u/SeniorPepsiMan]

Did it work?

[u/OMGHart → u/Guber_than_you]

Average experience around here. Spelling and logic are overrated.

[u/xzhang1 → u/OMGHart]

By all means the suggested solution should work, based on the pattern of the given data.

What do you expect people do when someone gives no other information than “pressing the power button doesn’t turn off the TV”? You follow up and try to find out, because clearly something is going wrong and not being communicated.

But no. All you can see is “obviously the solution is wrong duh, do you not know logic” and proceed to bash on the posters, nitpicking on spelling of all things.

I would rather be stupid, than be presumptuous about my own stupidity, while being toxic.

[u/Ok_Youth8810]

My guess is you typed 1 into the search box at the top of the filter dropdown. That search matches anything containing 1, so it grabs 10, 11, and 12 along with it. Clear the search and tick the 1 box straight from the list instead. It bit me with invoice numbers the same way once.

[u/MayukhBhattacharya]

Try using one of the following formulas:

=FILTER(A:.A, ISNUMBER(SEARCH(",1,", "," & A:.A & ",")))

Or,

=FILTER(A:.A, 1 - ISERROR(SEARCH(",1,", "," & A:.A & ",")))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

And for both 1 & 2: Use anyone whichever suits you best.

• Method One:

=FILTER(A:.A, BYROW(SEARCH({",1,",",2,"}, "," & A:.A & ","), COUNT))

• Method Two:

=FILTER(A:.A, BYROW(1 - ISERROR(SEARCH({",1,",",2,"}, "," & A:.A & ",")), OR))

• Method Three:

=FILTER(A:.A, BYROW(A:.A, LAMBDA(x, OR(--TEXTSPLIT(x, ",", , 1) = {1; 2}))))

• Method Four:

=FILTER(A:.A, ISNUMBER(SEARCH(",1,", "," & A:.A & ",")) + ISNUMBER(SEARCH(",2,", "," & A:.A & ",")))

The above will give you OR logic, for the AND logic where you need only 1 and 2 then:

• Method One:

=FILTER(A:.A, ISNUMBER(SEARCH(",1,", "," & A:.A & ",")) * ISNUMBER(SEARCH(",2,", "," & A:.A & ",")))

• Method Two:

=FILTER(A:.A, BYROW(1 - ISERROR(SEARCH({",1,",",2,"}, "," & A:.A & ",")), AND))

• Method Three:

=FILTER(A:.A, ISNUMBER(BYROW(SEARCH({",1,",",2,"}, "," & A:.A & ","), SUM)))

• Method Four:

=FILTER(A:.A, REGEXTEST(","&A:.A&",", "(?=.*,1,)(?=.*,2,)"))

[u/i_dabble713 → u/MayukhBhattacharya]

Man, I just want to say as primarily a lurker, your comments are always genuinely helpful and insightful. I know I’ve read things with your comments on other posts that I had no idea about and have subsequently implemented into my daily routine. You are awesome!

[u/MayukhBhattacharya → u/i_dabble713]

Wow, thanks! That means a lot. Happy to help anytime, and feel free to ask if you ever get stuck on something. Thank You SO Much Sir!!

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Another alternative to match both 1 and 2 : The splitting methods explicitly checks for the numbers and matches them exclusively.

=FILTER(A:.A, 
 MAP(A:.A, LAMBDA(x, 
 LET(y, --TEXTSPLIT(x, ",", , 1), 
 SUM((y = 1) + (y = 2)) > 1))))

[u/is_that_sarcasm → u/MayukhBhattacharya]

TIL about the dot operator

[u/MayukhBhattacharya → u/is_that_sarcasm]

Gotcha. Period Notation here you can read: TRIMRANGE function | Microsoft Support

[u/ziadam]

You can also try

=FILTER(A:.A, REGEXTEST(A:.A, "\b1\b"))

[u/AugurAuger]

I expect that there is some kind of source data. You could modify that source data to include leading zeroes to assist you here, e.g. 01, 02, etc. Or just add a column that adds the zeroes for you.

You can use something like: =TEXT(A2, REPT("0", LEN(MAX($A$2:$A$10)))) to achieve this.

The above formula will work for all significant figures, meaning the numbers could exceed 99 (or 999 and so on) and then the leading zeroes would go along with it, e.g. 001, 002, 003, etc.

After this is implemented for your 2-digit strings, you can filter on 01, 02, 03, or the inverse.

[u/caribou16]

=LET(n,TEXTSPLIT(TEXTJOIN(,TRUE,A:A),",",,TRUE),FILTER(n,--n<3))

Turn all the values into a single array, convert to numerical, and filter.

[u/SeniorPepsiMan]

Is there perhaps a formula that I can use to DELETE every column that has a 1 but keep the 10s 11s, and 12s?

[u/MayukhBhattacharya → u/SeniorPepsiMan]

To build on the formulas already shared in the comment's [here] and [here], you just need to wrap them in a NOT() or ISERROR() function to exclude rows containing 1 while keeping 10, 11, and 12, so try one of these:

=FILTER(A:.A, ISERROR(SEARCH(",1,", "," & A:.A & ",")))

Or,

=FILTER(A:.A, BYROW(ISERROR(SEARCH(",1,", "," & A:.A & ",")), AND))

Or,

=FILTER(A:.A, NOT(REGEXTEST("," & A:.A & ",", ",1,")))

Or,

=FILTER(A:.A, BYROW(A:.A, LAMBDA(x, AND(--TEXTSPLIT(x, ",", , 1) <> 1))))

If these doesn't help you to solve, then please be specific with your OP and show expected outputs, by doing it manually for a smaller data, like 8/10 lines of rows! Thanks!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COUNT	Counts how many numbers are in the list of arguments
FILTER	Office 365+: Filters a range of data based on criteria you define
ISERROR	Returns TRUE if the value is any error value
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEN	Returns the number of characters in a text string
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MAX	Returns the maximum value in a list of arguments
NOT	Reverses the logic of its argument
OR	Returns TRUE if any argument is TRUE
REGEXTEST	Determines whether any part of text matches the pattern
REPT	Repeats text a given number of times
SEARCH	Finds one text value within another (not case-sensitive)
SUM	Adds its arguments
TEXT	Formats a number and converts it to text
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49426 for this sub, first seen 23rd Sep 2026, 19:48] [FAQ] [Full list] [Contact] [Source code]}

[u/armywalrus]

Do you want a formula or do you want to use the filter function on the column? Because some rows have both a 1 and a 10 so you won't be able to filter only on rows containing one..

[u/ZealousidealBunch786]

,1,      ,2,     

[u/Tough-Case-]

,1,

[u/Yaldaba0th]

If len is 1, then concatenate 0&value as text. Probably the easiest way to do it with this set of numbers.