Excel formula - any ideas?

[anonymous-user]

I'd like to think I was fairly proficient with Excel, but I've met my match and I'm wondering if anyone here can help me please?

Let's say, I have a column of data. Each cell contains a string of text data:

e.g.
apple-toast-beer
apple-toast-monitor
car-computer-beer-phone
toast-monitor-cup
plate-glass-cup

I need to look for a certain text in each string, so I'm using the "find" function. But if the text isn't found in the string, it needs to look for a second piece of text. And the output needs to be whichever text string was found. But if it finds no match to either piece of text, it should return a "no match".

The format of the text in each cell isn't consistent which means I can't just split the string into the component parts before I search it.

So in the example above, let's say I am looking first for the text "cup" and if it can't find that text, I am looking for the text "toast". That would look like this:

toast
toast
no match
cup
cup

My problem is with using the "find" feature works ok, until if can't find the first piece of text and so I get the #VALUE! error. I can't get it then to go and look for the second piece of text. Or return a "no match" if it can't find anything.

I'm trying to do a workaround, but there are a few hundred thousand rows so it is becoming a pain!

Any ideas for the easiest way to achieve this?

Edited by anonymous-user on Wednesday 8th September 17:03

[nickd01]

Bit of a bodge, but something like this:

=IFERROR(IFERROR(MID(C5,SEARCH("cup",C5),5),MID(C5,SEARCH("toast",C5),5)),"No Match")

Or I'd look at doing Text-To-Columns on it and splitting it into separate columns

[anonymous-user]

nickd01 said:

Bit of a bodge, but something like this:

=IFERROR(IFERROR(MID(C5,SEARCH("cup",C5),5),MID(C5,SEARCH("toast",C5),5)),"No Match")

Or I'd look at doing Text-To-Columns on it and splitting it into separate columns

Actually, that's very good!

Would you mind taking it one step further please? How would I extend your function if I was also looking for a third term e.g. apple?

ETA : I think I could rewrite the function like this for three terms, but will check it now....

=IFERROR(MID(G2,SEARCH("cup",G2),3),IFERROR(MID(G2,SEARCH("toast",G2),5),IFERROR(MID(G2,SEARCH("apple",G2),5),"No Match")))

Edited by anonymous-user on Wednesday 8th September 17:30

[paulrockliffe]

What version of Excel are you using? If you have 2016 onwards you have a hidden tool called Power Query, which makes stuff like this (and everything else you'd normally use formulas for) pretty straightforward.

If you can, load the table in there and you can use the Replace function a few times to identify the strings you need. If you add a Custom Column you can use various text functions to get the answer you need.

Here's a good guide; https://www.myonlinetraininghub.com/searching-for-...

[anonymous-user]

paulrockliffe said:

What version of Excel are you using? If you have 2016 onwards you have a hidden tool called Power Query, which makes stuff like this (and everything else you'd normally use formulas for) pretty straightforward.

If you can, load the table in there and you can use the Replace function a few times to identify the strings you need. If you add a Custom Column you can use various text functions to get the answer you need.

Here's a good guide; https://www.myonlinetraininghub.com/searching-for-...

Thanks for that tip. I am using 2016 so will take a look at that.

The suggestion above from nickd01 also worked a treat.

I am realising I am only a beginner when it comes to Excel!

[paulrockliffe]

EddieSteadyGo said:

Thanks for that tip. I am using 2016 so will take a look at that.

The suggestion above from nickd01 also worked a treat.

I am realising I am only a beginner when it comes to Excel!

Yes, the formula approach works too and the 'right' approach really depends on context. If you just need a quick one-off bodgey thing then it's perfect. If you want to be able to easily build on it, easily update your source data, easily document the process and save yourself a load of time in the future, it's worth avoiding the formulas and learning Power Query and Power Pivot.

I don't mean the bodge thing disrespectfully at all, that's Excel's forte! What happened in the past was the data analysis world split into people doing it 'properly' on servers using SQL etc and amateurs making things happen sub-optimally in Excel, often with good reason and often because they don't know any better.

Then Microsoft dropped in Power Query and Power Pivot and said, "You don't have to bodge it anymore!". But no one was listening because the professionals left years ago and the amateurs were now experts at the bodges. And Microsoft did a great job of hiding it all away too.

For various really tedious and boring reasons I have to do lots of 'proper' stuff in Excel, which involves pulling data from proper systems, using Power Query to import it into Excel, building a load of analysis on top using DAX Measures and turning it into a relational database in Excel - You can create relationships in Power Pivot between tables that are like a top-level VLOOKUP between the tables. To give you an idea of the possibilities, I'm building analysis that is much closer to applications than spreadsheets, In the last 4 years I've only used Formulas for one thing - Dynamic titles, everything else is Power Query, relationships between tables and build the output in various Pivot Tables and Charts.

There's a legitimate argument that if you do it in Power Query, you might as well download Power BI Desktop at no cost though as the code that Power Query generates will copy-paste into there and pretty much just work, while giving you 100x as many options for getting the data to show you what you want and that's another thing that stops people really noticing Power Query in Excel. Most people learn Power Query in Power BI and then have no reason to ever open Excel again.

I'm going way off topic now, but if you're interested in making your life miles easier, Google some of that stuff, look on YouTube etc, there's a whole world of free learning out there.