Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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 "Amylou" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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? -- Biff Microsoft Excel MVP "Amylou" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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(ISNUMBE R(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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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!!!!! "bman" wrote: 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(ISNUMBE R(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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
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" wrote: 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? -- Biff Microsoft Excel MVP "Amylou" wrote in message ... 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! . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formula
=IF(ISNUMBER(SEARCH("USA",($E1))),"USA",IF(ISNUMB ER(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")) -- Biff Microsoft Excel MVP "Amylou" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Amylou" wrote in message ... 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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formula | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula, Conditional Formula Needed | Excel Discussion (Misc queries) | |||
conditional formula | Excel Worksheet Functions | |||
Conditional Formula to indicate Formula in cell | New Users to Excel |