View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Nick Hebb Nick Hebb is offline
external usenet poster
 
Posts: 162
Default Find wildcard text within a cell

If you want to do this without using VBA, you can do it with a Filter
and several formula columns.

1. Apply a filter to the range.
2. Click the filter dropdown arrow and select Custom.
3. Select "contains" and enter "~*~*Item". [The tildes are escape
characters to recognize the *'s since "*" is a wildcard chracter.]
4. First formula column:
=RIGHT(CELL_ADDRESS,LEN(CELL_ADDRESS)-FIND("""",CELL_ADDRESS,1))
5 Second formula column:
=LEFT(FIRST_FORMULA_ADDRESS,FIND("""",FIRST_FORMUL A_ADDRESS,1)-1)

Where CELL_ADDRESS is the relative cell address of the cell to be
searched and FIRST_FORMULA_ADDRESS is the relative cell address of the
cell containing the first formula.