Using IF statement on part of text content of a cell?

[lkqwesr]

Hi.

I'm using Excel 2002 on Windows XP Pro SP2. I have a bunch of text data
in cells that I want to break out into individual columns. The text
represents categories of data, such as:

Insurance, Legal, Management, Marketing, Software

Or:

Air Pollution Control, Environmental Products & Services, Ergonomics,
Health & Safety, Noise Protection

I currently have about 310 rows of data that includes the categories in
a single column -- column Q right now. There are nine main categories
and about 110 secondary categories. All of the entries in column Q are
helpfully separated by commas.

I've considered just doing Text to Columns and pushing the data into
its own columns, but since there are no blank entries in column Q, the
Text to Columns process becomes a laborious mess.

I'd like to break out the text such that at least the main categories
show up as column headers, and if that row's Q cell contains the
appropriate category information, mark an X under the appropriate
column. Such as if Q9 contains Insurance, Management, and Noise
Protection, I want it to put an X under an appropriately named column
for that category. If Q9 doesn't contain Legal and Software, then no X
should appear under those category columns, etc.

I have a list of all of the categories, which I can reference through
some sort of lookup -- if I knew how.

In column R, I tried a formula like this:

=IF(TRIM(Q4)="Legal",1,0)

Even if Q4 contains "Legal", Excel returns a 0, thinking it a false
match. I'm a little baffled as to why this is.

Can anyone tell me how to correct the statement to make it return a 1?
(and don't tell me to switch the position of the 1 and 0 in the formula
please. )

What I think I need is to know how to get at just some of the text in
Q4. Is there some sort of wildcard I can include in an IF statement to
get at only part of the text? Since the text in column Q is different
for virtually every row, I can't think of an easy way to do this, short
of doing a formula under as many category columns as I feel like
dealing with, then copying that formula across all rows.

If someone has a better solution than my manually keying in formulas
for every category column, such as somehow accessing the categories
from the list I have, I'm game for that, too.

Thanks in advance for your suggestions on this.

Mark

 

[lkqwesr]

Typical that I'm answering my own question, but I figured someone else
may be interested in the answer. I found this on another group, but I
don't understand why it works the way it does.

=IF(ISNUMBER(SEARCH("Legal",Q4)),"x","")

returns an x if the word Legal appears in Q4; otherwise it returns a
blank.

That does the trick nicely if all I want to do is search for single
words or phrases.

However, I'm looking to make it more complex. Now, I'm searching for a
way to make it search for multiple things in the same formula. Such as,
if the formula finds any of the words Insurance, Legal, Management in
cell Q4, I want to put an x in a column I have titled Business. I'll be
digging around with the OR function, but if anyone has any tips that
would save me some time, I would appreciate hearing them.

Thanks.
Mark

 

[lkqwesr]

I figure someone would have gotten to this eventually, but in trying
other sources, a friend came up with this solution for me, which I
share with those of you who may be looking for a similar solution to a
problem you're having.

=IF(OR(ISNUMBER(SEARCH("First",F10)),ISNUMBER(SEARCH("Second",F10)),ISNUMBER(SEARCH("Third",F10)),ISNUMBER(SEARCH("Fourth",F10)),ISNUMBER(SEARCH("Fifth",F10)),ISNUMBER(SEARCH("Sixth",F10)),ISNUMBER(SEARCH("Seventh",F10)),ISNUMBER(SEARCH("Eighth",F10)),ISNUMBER(SEARCH("Nineth",F10)),ISNUMBER(SEARCH("Tenth",F10))),"x","")

In this formula, if F10 contains any of the words listed above (First,
SEcond, Third, etc.), then the column with the formula in it is filled
with an x. Otherwise, the column remains blank.

Mark