Assigning a number value to strings

[JRD]

Example: (see explantion below)

Column A Answer
1 LMS
LADprox 3 (2 for LMS,0 for LAD prox and LADother,1 for RCA)
LAD other
RCA

2 LADprox 1 (1 for LAD prox, 0 for LADother)
LADother

3 LADother 2 (2 for LMS, 0 for LADother)
LMS

4 RCA 2 (1 for RCA, 1 for LCX)
LCx

5 LMS 3 (2 for LMS, 1 for RCA)
RCA

6 LADother
LCX (1 for LADother, 1 for LCX, 0 for LAD prox)
LAD prox

 

[JLatham]

A bit of twain brister. But I think I have the solution. It does give the
same results as your examples when tested against them.

One note: make sure you are consistent in the way the "LADother" and
"LADprox" are spelled in column A. In some cases you have a space after LAD
and in others you don't. The formula is set up without the space.

Here's my formula for row 1:
=IF(ISERR(FIND("LMS",A1)),IF(ISERR(FIND("RCA",A1)),IF(ISERR(FIND("LADprox",A1)),IF(ISERR(FIND("LADother",A1)),IF(ISERR(FIND("RCA",A1)),0,1)
+ IF(ISERR(FIND("LCX",A1)),0,1),IF(ISERR(FIND("RCA",A1)),0,1) +
IF(ISERR(FIND("LCX",A1)),0,1)+IF(ISERR(FIND("LADprox",A1)),0,1)+IF(ISERR(FIND("LADother",A1)),0,1)),IF(ISERR(FIND("LADother",A1)),IF(ISERR(FIND("RCA",A1)),0,1)
+
IF(ISERR(FIND("LCX",A1)),0,1)+IF(ISERR(FIND("LADprox",A1)),0,1)+IF(ISERR(FIND("LADother",A1)),0,1),1
+ IF(ISERR(FIND("LCX",A1)),0,1 + IF(ISERR(FIND("RCA",A1)),0,1)))),1 +
IF(ISERR(FIND("LCX",A1)),0,1)),IF(ISERR(FIND("RCA",A1)),2,2+1))

You'll notice in some places I used result values like 2+1 instead of just
plain 3. That is so if you need to change the value of various strings, it
will be easier.

Here it is with words in place of returned values, this may help you with
future changes also since it shows you where decisions are made and what
those are.
=IF(ISERR(FIND("LMS",A2)),IF(ISERR(FIND("RCA",A2)),IF(ISERR(FIND("LADprox",A2)),IF(ISERR(FIND("LADother",A2)),IF(ISERR(FIND("RCA",A2)),0,1)
+ IF(ISERR(FIND("LCX",A2)),0,1),IF(ISERR(FIND("RCA",A2)),0,1) +
IF(ISERR(FIND("LCX",A2)),0,1)+IF(ISERR(FIND("LADprox",A2)),0,1)+IF(ISERR(FIND("LADother",A2)),0,1)),IF(ISERR(FIND("LADother",A2)),IF(ISERR(FIND("RCA",A2)),0,1)
+
IF(ISERR(FIND("LCX",A2)),0,1)+IF(ISERR(FIND("LADprox",A2)),0,1)+IF(ISERR(FIND("LADother",A2)),0,1),"LADo
and LADp = 1 + any LCX and/or RCA")),"LMS=no,RCA=yes =
1"),IF(ISERR(FIND("RCA",A2)),"LMS only = 2","LMA and RCA = 2+1"))