•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Filtering function for Column information
Our take
Are you looking to enhance your spreadsheet functionality with a more flexible filtering approach? If so, consider this method for transforming specific words in a column. The current formula effectively filters keywords like "APPLE" and "BANANA" from a given text, but you can adapt it to replace certain terms with alternatives, such as returning "Other" for any unlisted words. This enables a clearer categorization of your data, allowing for improved analysis and organization. Let’s explore how to implement this adjustment seamlessly.
Hello all, I am currently running a the function below to filter out words in a specific column (changed words for simplicity). I was wondering if there were a function that can be added on to the function below so that any specific words would instead come back as other
=LET(
_keywords, {"APPLE","BANANA","GRAPES","PINEAPPLE"},
_text, TEXTSPLIT(A1, , " "),
_filter, FILTER(_text, BYROW(1 - ISERR(SEARCH(_keywords, _text)), OR)),
_joined, TEXTJOIN(" ", TRUE, _filter),
_joined)
EX. Column A1 says Apple basket with Banana bread, which gives me Apple, Banana
But what can I do so that when A2 says Cookie gift and Apple basket, it sends me back with Other, Apple
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Ordering text after filterHello All, I am attempting to extract information from a single cell and want to find the function that allows for it in order in which the text occurs in the cell. I currently have the =LET function pulling the information as required into a separate cell, but its not in order in which it appears in the cell submitted by /u/ActAutomatic6726 [link] [comments]
- Assistance with advanced filter optionsI have a report that is being generated through some janky software, and unfortunately the way I am getting the excel document can't be changed. This is a very basic example of what the sheet looks like, the actual generated report is thousands of rows: https://imgur.com/a/d0jNtNx For example, I want to filter for Bob's name in column A and also see all of Bob's associated comments in column B. If I apply a filter for Bob in column A, it hides everything in column B that are the relevant comments (obviously). To clarify, I want a filter to make it look like this: https://imgur.com/a/pgVJsLm And not like this: https://imgur.com/a/wCev8uH Perhaps the filter function isn't the right approach, so I am open to alternatives. submitted by /u/drspudbear [link] [comments]
- Filter/search when the keyword is short and genericI was using a simple FILTER(range,ISNUMBER(SEARCH("keyword"),column),0) to filter a dataset. The problem arose when I have to find using very short and generic keywords such as "car", which the formula also returned results like "care, card, carpets, carrying, carton, etc" Is there any way to just return results with "car" and "cars"? Thank you so much. submitted by /u/Aggravating-Plum-534 [link] [comments]