Conditional Formula

[Amylou]

What I have is a spread sheet of information from a vendor about several
different products. One specific column holds a single cell with what is
usually a paragraph of information about the product represented in that row.
What I need to get out of each “paragraph†are specific key bits of
information and having to go through each paragraph for a minimum of key
points just seems to take forever.

What I would like to do is set up a few extra columns.
For example:

A(acid free) B(lightfast) C(made in USA or Imported)

…have these columns look through the “paragraph†and return either a “Y†or
“N†for A & B and either “USA†or â€I†for column C.

Any help would be greatly appreciated!

 

[Barbara Wiseman]

I am not sure exactly what you are trying to do, but if you are to find out
if a cell that has text in
it contains a certain word then this previous answer may help

http://tinyurl.com/ybdup95
if you want a preview use this link.
http://preview.tinyurl.com/ybdup95

If this is not what you require, post back with more detail ie, example of
the paragraph, which cell it is in, what should appear in other cells and
why.

Hope that helps,
Barbara

 

[T. Valko]

C(made in USA or Imported)

either "USA" or "I" for column C.

Will the cell (paragraph) *always* contain one or the other?

Can you post a sample paragraph so we can see what that looks like?

 

[bman]

Presuming col E has your product text data,

In col A, corresponding row, put
=IF(ISNUMBER(SEARCH("acid free",($E1))),"Y", "N")

In Col B
=IF(ISNUMBER(SEARCH("lightfast",($E1))),"Y", "N")

In Col C
=IF(ISNUMBER(SEARCH("USA",($E1))),"USA",IF(ISNUMBER(SEARCH("import",($E1))),"Imported","No"))

Then copy each Col down for as many rows as you have data. This should work
for Capital letters as well as lower case in the data.

This is presuming your data (Col E")is consistent and clean, e.g. no
extraneous spaces, spelling errors or something like "not imported" is used
which would give the opposite result you intended.

Hope this helps.

 

[Amylou]

Thank you bman, it worked perfectly and as long as there are no spelling
errors, abreviations etc it is going to save me hours every month!
I LOVE EXCEL!!!!!

 

[Amylou]

Here is the requested paragraph:

Regardless if you have a new or used machine there are always questions
concerning various attachments how they are used with the machine and what
functions they perform. This book covers a number of the top attachments as
well as sewing feet in order to provide sewers of all levels the information
and instruction they need to find the right attachment for any project. It
is the reference that you will be able to refer to over and over again. 144
pages.

As you can see it does not always say "Made in USA" or "Imported" , however
there is another column that picks the actual CO from our vendors database.
The reason for only "USA" or "Imported" simplifies it for our customers. If
it matters to someone whether the product is made outside of the US they
don't really care where the exact origin is. Using bman's formula my cell
picks up USA or Imported from the "paragraph", but if neither is present the
cell will get the information from the other column it is available in.
Did this clearify it for you T.Valko?

 

[T. Valko]

=IF(ISNUMBER(SEARCH("USA",($E1))),"USA",IF(ISNUMBER(SEARCH("import",($E1))),"Imported","No"))

Using bman's formula my cell picks up USA
or Imported from the "paragraph", but if neither
is present the cell will get the information from
the other column it is available in.

Ok, bman's formula will return "No" if neither are found. Is that what you
want?

You can shorten that formula by a few keystrokes:

=IF(COUNT(SEARCH("USA",$E1)),"USA",IF(COUNT(SEARCH("import",$E1)),"Imported","No"))