how to use a wildcard with an IF formula to search for text in a cell

**nje** · 10-23-2012, 05:01 AM

I have attached a sample sheet.
What i have done so far is working but it needs a tweak to get it to do exactly as I want.
I have put a formula in column E that adds the number from column J when it sees the word 'utilities' in column D.
My problem, which I hadn't anticipated is that if any other words appears in column D with the word 'utilities' for example
'Plot 1 utilities' it doesn't add the number in column J. I need it to recognise and change when it sees a range of names from 'Plot 1 utilities' through to ''Plot 11 utilities. I don't know if it's a wildcard I need or not, I have tried putting * and ? in the formula but haven't got anything to work yet
Thanks

**Pete_UK** · 10-23-2012, 06:48 AM

Put this in E6:

Formula:

Please Login or Register  to view this content.

then copy down.

Hope this helps.

Pete

EDIT: You do have to spell utilities correctly in D10.

**nje** · 10-23-2012, 07:15 AM

Thanks Pete, works like a charm. I had already noticed that the word 'utilities' had to be spelt correctly for it to work, because I had spelt it wrong in another cell and it would work. Thanks for your help, it's a shame it can't allow for the odd typo though

**Pete_UK** · 10-23-2012, 07:32 AM

Yes, but think how many combinations of mis-spelt UTILITIES there could be. If you could guarantee always to type the first 4 characters correctly, then you could just have "util" instead of "utilities" in the SEARCH function - case is not important.

Hope this helps.

Pete

**nje** · 10-23-2012, 07:40 AM

Thanks again Pete, I've changed it to 'util' now. I think it will be easier to get the first four letters right than the whole word.
I appreciate your help and can now mark this as solved. Thanks again