search a string in an excel cell

[sanjog]

within a single cell I have following character values separated by ,

VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE,AS,CN,CO

<tbody>
</tbody>

I have a lots of such entries that vary along the row for the same column no

I want to write a script to check 3 conditions and return the result accordingly

1. If the string contains "US" return 1
2. If the string contains "UK" return 2
3. If the string contains "US" and "UK" return 3
4. If the string contains "US" and "UK" and "FR" return 4

any formula or a VB script to do this effectively for variable rows and fixed column ?

Sanjog

 

[Rick Rothstein]

Doesn't CountIf(A1,"*UK*") return just 0 (if "UK" is not found in A1) or 1 (if "UK" is found in A1, no matter how many times)?

@PGC,

Gulp! You are right... I forgot... I am counting a single cell, not a range. Thanks for noting that for me.


@sanjog,

As PGC noted, I included more than was necessary in my formulas. Here they are revised in accordance with PGC's comment...

Returning 0 for blank cells...

=COUNTIF(A1,"*US*")+2*COUNTIF(A1,"*UK*")+(COUNTIF(A1,"*US*")+2*COUNTIF(A1,"*UK*")=3)*COUNTIF(A1,"*FR*")

Returning the empty string ("") for blank cells...

=IF(A1="","",COUNTIF(A1,"*US*")+2*COUNTIF(A1,"*UK*")+(COUNTIF(A1,"*US*")+2*COUNTIF(A1,"*UK*")=3)*COUNTIF(A1,"*FR*"))