Excel IF Forumla finding part of a word

funkylad20 | 13:31 Thu 18th Feb 2010 | Technology

1 Answers

I have a cell with text in it. Within this text is 'Cap' or 'Rev'.
Using IF formula and presumably wildcards, how can I establish if a cell contains 'Cap' I want a result to be 'Capital' and if it contains 'Rev' I want it to return 'Revenue'.
I have tried using =if(A1="*Rev","Revenue","Capital") but it returns ALL as Revenue, despite some cells containing no 'Rev' but does contain 'Cap'

Adobe Photoshop Elements 8

Powerpoint 2OO3/2, my "set transparent colour" won't work, why not? It won't get rid of the colour I want!

Answers

Only 1 answer

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.

ABerrant

Perhaps a bit clunky but this formula should work...

=IF(ISERR(FIND("Cap",A1))<>TRUE,"Capital",(IF
(ISERR(FIND("Rev",A1))<>TRUE,"Revenue","")))

"Cap" and "Rev" are case specific and ISERR is there to thwart any errors if neither "Cap" nor "Rev" are present.

Only 1 answer

Do you know the answer?