Excel search help

[mrcharly-YHT]

I want to find all cells that contain two words.

Not adjacent, but placed in any order in a string.

The workbook is large (over 100 sheets with hundreds of rows of data on each).

Is this possible in excel?

[jiberjaber]

Sounds like you would need a macro in order to do that, other alternative though more manual would using the exiting search & replace with the scope of the search set to "Workbook".

[Manotea]

Let me Google this for you..

I suspect the answer to your question is probably some variant of ...https://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba

Just need to tweak the match criteria... 🤠

[ian]

Why not just use two SEARCH functions and an AND?

[ElyDave]

Conditional formatting to highlight the cells?

[mrcharly-YHT]

Conditional formatting to highlight the cells?

There are over 100 sheets in this ODS workbook.

<deity> onna bike, I really wish they'd put this data into something sensible, like YAML or JSON. Then I could just grep it.

[Jaded]

Take the pain of exporting the data, then do it a sensible way?

[drossall]

Apparently you can use RegExs in Excel, if you also use a bit of VBA, e.g.

https://www.myonlinetraininghub.com/regex-regular-expressions-excel

https://www.ablebits.com/office-addins-blog/excel-regex-formulas/

[lissotriton]

Take the pain of exporting the data, then do it a sensible way?

XKCD has the answer.


https://xkcd.com/2180/

[ian]

The old joke, why spend four minutes doing something in Excel when you can do it in just four hours with R.

[ElyDave]

Conditional formatting to highlight the cells?

There are over 100 sheets in this ODS workbook.

<deity> onna bike, I really wish they'd put this data into something sensible, like YAML or JSON. Then I could just grep it.

Copy and paste formats

[Jaded]

Take the pain of exporting the data, then do it a sensible way?

XKCD has the answer.


https://xkcd.com/2180/

 

[HTFB]

Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

[mrcharly-YHT]

Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

365

I really need to be able to search the entire workbook. There are over one hundred sheets.

[Lightning Phil]

Excel is fine as stand alone, when the data doesn’t need to interface with anything else, and it’s treated as end user computing. You built you support it, don’t expect IT to try and get to grips with it and bail you out when so and so leaves.

[Lightning Phil]

The find dialog has the option to search entire workbook. I think the search phrase can be a regular expression as well.  That is likely your best option if VBA is beyond you.

[HTFB]

Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

365

I really need to be able to search the entire workbook. There are over one hundred sheets.

Enumerating worksheets means it's VBA time. I'll have a go at a cut-and-paste macro at lunch [edit: but not today.]

[Manotea]

Hmm... how did you get on? could be done using powerquery to consolidate the data into a single table, then merging all columns then applying a filter, though the last might be easier said than done..
Might have had a go but I didn't because the solution really depends on what you want to do with the cells once you've found them ..🤠

[mrcharly-YHT]

I moved on to other things.

Now very glad I didn't spend a lot of time on this because it turns out that the data I needed doesn't have anything in it that identifies it. There is a name, but nothing in a description or any flag.

So it is impossible to search the spreadsheet and extract a list. Relies totally on having teh knowledge.

[Manotea]

Tell me about it....

Currently trying to cross reference a bunch of tables which look as though they have direct relationships but don't because the key fields were set by different people to different standards. I've had a go at tidying the data but basically will have to throw the whole thing back at the users.

It's the only way to be sure...

[mrcharly-YHT]

Oh this is nothing like a jumble of key names or anything. The information is text. No identifier. In a spreadsheet with tens of thousands of entries.