Here's another approach.
It avoids the error generated by SEARCH in the absence of the search string by using LEN and SUBSTITUTE.
D2 (and filled down to D10): =IF(LEN(A2)>LEN(SUBSTITUTE(A2,D$1,"")),B2,"")
Like Wayne's version, this formula returns a null string if the search string (retrieved from D$1) is not present in the original string in column A. The null string is not a 'blank' cell, but is as close as one can get, using a formula in Numbers.
Thanks for the replies. But the strict typing combined with lack of any way to denote empty or null values of a certain type makes it really, really unfriendly.
I was trying to total a range, or replicate a cascading running total down a column in sheet of content which was nicely formatted with white space between groups of entries. Therefore, if a formula derived from column data to the left is copied all the way down a column, there are going to end up being zero values. Shouldn't matter, and is easy to suppress zero display in er, uh, that other package claiming by its name to be excellent, but ...
So I'll omit the details of griping about number, time and date formatting, or the number of clicks and layers required, or the lack of tool bar buttons or a way to customize or add buttons and just say did they hire people from Microsoft to come over and do this product?
But eventually I just threw in the towel and let my column have lots of extraneous zero values, and spent an hour setting up a custom format that would suppress zeros, use parenthesized display for negatives, and keep two fixed decimal places. This is otherwise known as standard accounting format and has been around at least a couple centuries I know of, so I'm just a little bit surprised it's not a choice in Numbers.
Before everyone starts replying with all kinds of hints, yes I know all these things are there. But if you want them together you have to build your own, and it can take a long time so if your time is money (I'm a consultant who bills hourly and spends unbillable hours trading, so time is definitely money) then the cost of use is orders of magnitude more than any cost of software.
Maybe when I get it all set up and customized it will feel more effective. There's plenty to like and it's really been since Trapeze -- whoa, going on 15 years ago? -- since someone realized a spreadsheet is not equivalent to a page. But it's still rough around the edges and needs some serious tuning based on metering user actions. Also, clicks and display of resulting selections seem to deviate from Mac standards in frustrating ways. They're trying to give clicks in the same region different meanings depending on other context. I understand the desire, but it's a UI no-no.
"er, uh, that other package claiming by its name to be excellent"
Not sure why you are being so coy about naming MS Excel; it is part of your user name after all.
No matter. The advice around here has generally been to use the tool that fits the job.
PS: If you want to see changes in the way Number works, those wants should be expressed to Apple, not to the participants in these user-to-user communities. Feature requests may be made through Provide Numbers Feedback, found in the Application menu ("Numbers" menu in Numbers). The link will also take you to the feedback page.