Help filter from a range using referenced text

**iantoday** · 12-11-2022, 05:49 PM

Hello,

I've just joined after purchasing a copy of Microsoft Office for the first time in years.

I've got a formula that works great in Google Sheets but I need the equivalent in Excel due to data processing limitations.

The formula looks at a one-column range - A3:A - and displays in the next column (column B) all items that contain the text provided in a referenced cell (B2 in the formula below), and formats the result as text.

The Google Sheets formula is:
=filter(A$3:A, regexmatch(A$3:A, TO_TEXT(B2)))

Does anyone know how it might be done? I've looked at various articles but have not been able to find what I'm looking for.

Thank you for your help.

Ian

**FlameRetired** · 12-11-2022, 06:11 PM

iantoday welcome to the forum.

Please update your profile to reflect which Excel product you are using. Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

**iantoday** · 12-11-2022, 06:40 PM

Hi Dave, this is so frustrating! I thought I had already specified that, it's shown below my name, it says "MS-Off Ver Excel 16.67", but if not, I have no idea know where to or perhaps don't even know what you're referring to. I do have MS365, although I think it isn't linked to the licence I've purchased, no idea what you mean by a name for it! The name is "Office 365" as far as I know.

The formula I'm looking for is one column using one criterion, this should be simple but I'm frustrated at each and every step. It's why I've not used Windows for years.

I am attaching another screenshot... if I can, they have been uploaded but are nowhere to be seen. You say to look in Excel under the File menu for Account. As it if it would be that easy. There is no "Account" at all under the File menu.

Maybe you mean "Office Home & Business 2021" which is in the Excel menu when I click on "About Microsoft Excel". It is for Mac.

**FlameRetired** · 12-11-2022, 10:38 PM

I have no idea know where to or perhaps don't even know what you're referring to. I do have MS365, although I think it isn't linked to the licence I've purchased, no idea what you mean by a name for it! The name is "Office 365" as far as I know.

Maybe you mean "Office Home & Business 2021" which is in the Excel menu when I click on "About Microsoft Excel". It is for Mac.

These two are two different products. If you click on File >> More >> Account you will see the MS logo in the right hand frame. Underneath that logo will be the product number. I mentioned 365 separately because in addition there is a version number (yes it is confusing they are using version number inconsistently) further down the frame underneath About Excel. If you have 365 please post both those numbers.

**Gregb11** · 12-11-2022, 11:16 PM

Please read the yellow banner at the top of the page. It's always best to attach a sample workbook - and give a mock-up of your expected results. Short of that, I'm guessing that maybe this formula in B3, with the word in B2:

=FILTER(A3:A1048576,ISNUMBER(SEARCH($B$2,A3:A1048576)))

See attached.

**iantoday** · 12-12-2022, 06:18 PM

Thank you. There is no "More" option in the File menu, as my earlier screenshot shows. I've attached another screenshot giving the result for the word "account" in Help.Screenshot 2022-12-12 at 22.13.17.jpg

**iantoday** · 12-12-2022, 07:51 PM

Thanks Greg. I did read the yellow banner and intended to attach example data. I created some but couldn't get it working properly. The formula you gave worked perfectly. For some reason, the FILTER function wasn't available to me yesterday, but it is now.

If you know of a way to make it so tapping enter/return to toggle cell editing on/off, I would be interested. The only option I've found is where to move the focus when pressing enter (down, right, left, none).

Help filter from a range using referenced text

LinkBack

Thread Tools

Rate This Thread

Display

Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Re: Help filter from a range using referenced text

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA to filter multiple pivot tables on text in a referenced cell

[SOLVED] Count the cells within a named range referenced by text in a cell

[SOLVED] Text Wrap - Automatically expand based off of referenced cell w/text from another sheet?

[SOLVED] macro to filter a range that has text

Filter Pivot Table between dates referenced in cell

Filter Pivottable on a fixed referenced cell.

How to filter a range of cells for specific text?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions