Related Posts
Chief
Where would you travel to in India during May?
Please reach out if you need a referral at IBM
Happy weekend, everyone
Cloud surfing to Vegas.
Additional Posts in Excel Genius
Visual Storyteller
Would anyone be willing to teach me vlookup?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Note that “abcd” also stars with “abc” so you don’t need to use OR.
Assuming you meant to filter on “abc” or “defg” I would just run the filter twice and VSTACK it.
=VSTACK(FILTER(column, LEFT(column, 3)=“abc”),FILTER(column,LEFT(column,4)=“defg”))
Subject Expert
In this specific case (assuming the “defg” case), yes, although the order would be messed up, as well as being at least 2x more time-consuming, plus any additional from the =VSTACK() (never tested its performance, but I’d assume it’s not that great)
But my point is mainly about “why” use 3 formulas when one can do the trick
Please use ChatGPT
To create a FILTER formula that brings any data from a column if the first 3 letters match “abc” or the first 4 letters match “abcd,” you can use the FILTER function combined with the LEFT function and the addition of an array formula. You cannot directly use OR in the FILTER function, but you can achieve the desired effect with an alternative approach.
Here’s how you can do it in Google Sheets:
=FILTER(A:A, (LEFT(A:A, 3) = "abc") + (LEFT(A:A, 4) = "abcd"))
In this formula:
• A:A is the range of the column you want to filter.
• LEFT(A:A, 3) = "abc" checks if the first 3 letters match “abc”.
• LEFT(A:A, 4) = "abcd" checks if the first 4 letters match “abcd”.
• The + operator is used to simulate an OR condition because it will be true if either condition is true.
This approach ensures that the FILTER function includes rows where either of the conditions is met.
If you are using Excel, you can use the following array formula (press Ctrl + Shift + Enter to enter the formula):
=FILTER(A:A, (LEFT(A:A, 3) = "abc") + (LEFT(A:A, 4) = "abcd"))
This will achieve the same result, filtering the data based on the given conditions.