If Search Issue

**jstu9** · 03-04-2015, 01:21 PM

I have an issue which i almost have solved with one hiccup.

I have a cell of information that looks like: A1; A4; C6; H9
It's just text. I then have a series of columns that looks to see if A1, A2, A3 etc is in that original cell.

=IF(ISNUMBER(SEARCH(F$1,Main!$AY9)),"L","")

This is basically the formula I used. It searches AY9 in "Main" to see if the contents in F1 are in that cell. Works great.

The problem comes if the info is: A1; A6; C12

The column for C1 and C12 are both triggered and the "L" will be in both columns. And I would only want it in C12. I thought about doing another search where if the cell contains C12 then don't put the L in C1, but the cell might: A1; H3; C1; C12 and then I would want it in both cells.

Is there a way I can differentiate between C1 and C12 with some formula?

Thanks.

**whero** · 03-04-2015, 09:09 PM

So you're searching for a text string, but only wanting to find some occurrences of that string, and omit others. For example, you want to find the text "C1" which could be anywhere in a cell, but not if it's followed by a character other than a semicolon.

I have a solution which seems to work for me, but it's not particularly elegant (in fact it's pretty ugly - I'm sure there's a "cleaner" option). It first checks whether "C1" appears in the target cell at all and returns "" if it does not. If "C1" is found, it then checks for two other conditions: (1) Is "C1" followed by a semicolon, or (2) is C1 the last two characters in the cell. If either of these conditions are met it returns the value "L", else it returns "".

Here it is:
=IF(NOT(ISNUMBER(SEARCH(F$1,Main!$AY9))),"",IF(OR(MID(Main!$AY9,SEARCH(F$1,Main!$AY9)+LEN(F$1),1)=";",SEARCH(F$1,Main!$AY9)+LEN(F$1)=LEN(Main!$AY9)+1),"L",""))

**FDibbins** · 03-04-2015, 09:24 PM

Hi, welcome to the forum

I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.