Create helper column which returns a text string based on multiplecriteria.

[Pierre]

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*"Production",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

 

[T. Valko]

Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))

 

[Pierre]

Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),­"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))

--
Biff
Microsoft Excel MVP










- Show quoted text -

Thank you, yet once again. I annoint you: "Sir Genius".

Pierre

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),­"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))

--
Biff
Microsoft Excel MVP










- Show quoted text -

Thank you, yet once again. I annoint you: "Sir Genius".

Pierre