Querying part of a cell entry

**Clash** · 07-13-2010, 10:44 AM

Hi, once again,

I have another one,

In column C, I have a number of ethnic groupings, can in ask for part entry in a cell, i.e. White instead of White British and the query return all entrys with the word White in them?

many thanks

**NBVC** · 07-13-2010, 10:58 AM

Say your data is in column X.

In Y2, enter =IF(ISNUMBER(Search(C1,X2)),Count(Y$1:Y1)+1,"") and copy down

Then in Z2: = MAX(Y:Y)

then to extract matches:

=IF(ROWS($A$1:$A1)>$Z$2,"",INDEX(X:X,MATCH(ROWS($A$1:$A1),$Y:$Y,0))

copied down and to next column(s) if necessary to grab adjacent data.

Adjust all ranges/references to suit.

**Clash** · 07-14-2010, 09:22 AM

Thanks NBVC, but it returns the all that is in cell i.e. White British and not just White, as i have 15 white catergories I was hoping to combine them as one and all others as another.

**NBVC** · 07-14-2010, 09:27 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!