| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create helper column which returns a text string based on multiplecriteria.
Have a column of data containng production codes, and need a helper
column to categorize them. Need to identify a few characters within the cells contents, and return another value based on what it finds: Source data looks like this: V009.ELE120 V144.128 V785.ELE150 V450.ELE22 T543.ELE295 Y564.ELE965 G125701.DYS021 If the cells contents contain a ELE120 or ELE150 a "Production" would be returned. Any other ELE's (not containing the 120 or 150 immediately following) would return "Sunshine". If the cell contains DYS, another value would be returned. Data to be looked up is preceded by the period. All other characters can be ignored. Tried: =IF(A1="*ELE120*","Production",IF(A1="*ELE150*"Pro duction",IF (A1="*DYS*","System"))) But the wildcard of having ELE coupled with 2 or 3 additional digits make this formula cumbersome. All the other ELE's would be something else. I think there's something wrong with the wildcard* character. ideas? Thanks for any help. Pierre |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create helper column which returns a text string based on multiple criteria.
Try this:
=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production" ,IF(COUNTIF(A1,"*.ELE*"),"Sunshine",IF(COUNTIF(A1, "*.DYS*"),"System",""))) -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a column of data containng production codes, and need a helper column to categorize them. Need to identify a few characters within the cells contents, and return another value based on what it finds: Source data looks like this: V009.ELE120 V144.128 V785.ELE150 V450.ELE22 T543.ELE295 Y564.ELE965 G125701.DYS021 If the cells contents contain a ELE120 or ELE150 a "Production" would be returned. Any other ELE's (not containing the 120 or 150 immediately following) would return "Sunshine". If the cell contains DYS, another value would be returned. Data to be looked up is preceded by the period. All other characters can be ignored. Tried: =IF(A1="*ELE120*","Production",IF(A1="*ELE150*"Pro duction",IF (A1="*DYS*","System"))) But the wildcard of having ELE coupled with 2 or 3 additional digits make this formula cumbersome. All the other ELE's would be something else. I think there's something wrong with the wildcard* character. ideas? Thanks for any help. Pierre |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create helper column which returns a text string based onmultiple criteria.
On Mar 27, 3:44*pm, "T. Valko" wrote:
Try this: =IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production" ,IF(COUNTIF(A1,"*.ELE*"),*"Sunshine",IF(COUNTIF(A1 ,"*.DYS*"),"System",""))) -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have *a column of data containng production codes, and need a helper column to categorize them. Need to identify a few characters within the cells contents, and return another value based on what it finds: Source data looks like this: V009.ELE120 V144.128 V785.ELE150 V450.ELE22 T543.ELE295 Y564.ELE965 G125701.DYS021 If the cells contents contain a ELE120 or ELE150 a "Production" would be returned. *Any other ELE's (not containing the 120 or 150 immediately following) would return "Sunshine". If the cell contains DYS, another value would be returned. Data to be looked up is preceded by the period. *All other characters can be ignored. Tried: =IF(A1="*ELE120*","Production",IF(A1="*ELE150*"Pro duction",IF (A1="*DYS*","System"))) But the wildcard of having ELE coupled with 2 or 3 additional digits make this formula cumbersome. All the other ELE's would be something else. *I think there's something wrong with the wildcard* character. ideas? Thanks for any help. Pierre- Hide quoted text - - Show quoted text - Thank you, yet once again. I annoint you: "Sir Genius". Pierre |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create helper column which returns a text string based on multiple criteria.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Pierre" wrote in message ... On Mar 27, 3:44 pm, "T. Valko" wrote: Try this: =IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production" ,IF(COUNTIF(A1,"*.ELE*"),*"Sunshine",IF(COUNTIF(A1 ,"*.DYS*"),"System",""))) -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a column of data containng production codes, and need a helper column to categorize them. Need to identify a few characters within the cells contents, and return another value based on what it finds: Source data looks like this: V009.ELE120 V144.128 V785.ELE150 V450.ELE22 T543.ELE295 Y564.ELE965 G125701.DYS021 If the cells contents contain a ELE120 or ELE150 a "Production" would be returned. Any other ELE's (not containing the 120 or 150 immediately following) would return "Sunshine". If the cell contains DYS, another value would be returned. Data to be looked up is preceded by the period. All other characters can be ignored. Tried: =IF(A1="*ELE120*","Production",IF(A1="*ELE150*"Pro duction",IF (A1="*DYS*","System"))) But the wildcard of having ELE coupled with 2 or 3 additional digits make this formula cumbersome. All the other ELE's would be something else. I think there's something wrong with the wildcard* character. ideas? Thanks for any help. Pierre- Hide quoted text - - Show quoted text - Thank you, yet once again. I annoint you: "Sir Genius". Pierre |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I insert 'carriage/line returns' in text string? | Excel Worksheet Functions | |||
| Help formula that returns a text string from another sheet | Excel Worksheet Functions | |||
| Create a 'text' string based on whether values appear in other cel | Excel Worksheet Functions | |||
| use concatenate function to put carrage returns in a text string | Excel Worksheet Functions | |||
| What is a helper column? | Excel Discussion (Misc queries) | |||
Linear Mode
