If cell contains certain text and certain text then return predefined text

Hi all,

Apologies if this has been covered elsewhere, I've had a good look through and can't find anything quite specific enough.

I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.

For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol

So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.

I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")

I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.

Any ideas?

Is your data all in that format, i.e. you want the first 2 words and the last word?

Pete

Hi Pete,

I'm afraid not, although it always either the first word, 2 words or 3 words (something in the middle) then a group of words, but sometimes something on the end. Here is an example of the real data with what I would need it to show, the above was just an example to try to simplify things a little:

Merit Award 2 Terms (0.5T PE) in Law, Economics and Finance = Merit Award in Law, Economics and Finance
Merit Award 3 Terms in Arts = Merit Award 3 Terms in Arts
Higher Education Award 3 Terms in Law, Economics and Finance (progressed from 1 Term PE) = Higher Education Award in Law, Economics and Finance

I don't mind writing out a string of IF functions for all the possibilities if neccessary.

Well, there is a slight pattern there - anything in brackets is ignored. You also omit the word Terms (and a number), but only for the first and third example - why is the second one different?

With such variations, you will need to define your requirements much more clearly than just these three examples.

Pete

Thanks again Pete.

Please can you help help me out with defining the entry for the function I haven't really got much further than basic IF functions.

Apologies if this sounds fecetious in anyway but the second one is different because it just is Our course titles are laid out in that way depending on the structure of the course (Not sure if I mentioned before but this is data for work so unfortunately I have no control of the format it comes to me in). Unfortunately I have a whole list of data in these formats. Luckily the example I have given gives the 3 different variations on structure, the rest of the titles just have differing ammounts of terms etc.

Hope you can help,

To get rid of the bracketed items you can use Find & Replace in one simple operation. Highlight the column containing the data (or copy it to another column and highlight that if you want to retain the original text), then CTRL-H, then:

Find What: enter "(*) " without the quotes - that is a space at the end
Replace with: leave blank
Click Replace All

You can do a similar thing for the Terms - use " Terms" in the Find section (now the space is at the beginning), then you can just get rid of the numbers. Obviously, this will get rid of all the Terms, but unless you have some way of distinguishing them then it's either all or nothing.

Hope this helps.

Pete

Try this

=IF(AND(ISNUMBER(FIND("Vauxhall Corsa",A1,1)),ISNUMBER(FIND("Petrol",A1,1))),"Vauxhall Corsal Petrol","")