If cell contains

[pmcalinden]

Hi,

Sorry this maybe a bit newbie for you guys but formal excel training.

Currently trying to identify if a cell contains the letters, EX, VG++, VG+, VG, etc ect, the cell also contains other text which can be ignored, I then want to place the identified characters in the sell in which the formula is presented.

Tearing hair out so any help apprciated. Have tried numerous variation and this is the latest not working incarnation before turning to your goodselves.

=IF(N2="EX",EXCELLENT,0),IF(N2="VG++",VERYGOOD,0),IF(N2="VG+",VERYGOOD,0),IF(N2="VG",VERYGOOD,0),IF(N2="VG-",VERYGOOD,0),IF(N2="NM",NEARMINT,0),IF(N2="G+",GOOD,0)

 

[Trevor G]

Welcome to the Board

Try this, basically the false statement is another if not closing brackets until the end.

=IF(A1="Ex","Verygood",IF(A1="VG++","Verygood",IF(A1="VG+","Good",IF(A1="VG","Good",""))))

 

[MrKowz]

Welcome to the forums!

One possibility:

=IF(N2="EX","Excellent",IF(OR(N2={"VG++","VG+","VG","VG-"}),"Very Good",IF(N2="NM","Near Mint",IF(N2="G+","Good",0))))

Or another way:

=LOOKUP(N2,{"EX","G+","NM","VG"},{"Excellent","Good","Near Mint","Very Good"})
*Note that in the LOOKUP formula, you don't need to state all of the VG statements. That has to do with being the "closest alphabetical match".

 

[pmcalinden]

Thanks for the welcome and assitance.

In terms of results, I am still nto ther yet. I am providing a couple of examples to see if I can make it any clearer as my explantion may not have been the best.

Cell Contents
<table width="457" border="0" cellpadding="0" cellspacing="0"><col width="457"><tbody><tr height="20"> <td class="xl63" style="height: 15pt; width: 343pt;" width="457" height="20">SONNY ROLLINS IN JAPAN GI UK ORIGINAL 1973 EX RARE</td> </tr></tbody></table>
The EX is obviously the info I wish to identify and replicate. Under the first of MR Kowz options I recieve a 0 (or false) despite changing the formula to upper case to help identify and it definetly appearing in the cell

The second option

cell contents

<table width="457" border="0" cellpadding="0" cellspacing="0"><col width="457"><tbody><tr height="20"> <td class="xl65" style="height: 15pt; width: 343pt;" width="457" height="20">GEORGE WALLINGTON KNIGHT MUSIC GI RECORDS EX LP</td> </tr></tbody></table>
Provides me with a result of "good" despite the apperance of "EX" in the cell.

hope this helps and thanks for yours.