Search for a partial string in the excel cell using VBA or Excel formula

Question

I have an Excel sheet with columns template and template name, I have to fill the template name column with a partial string in template column:

    Template                                 Template Name
    This is String  
    String line contains data
    This is Int 
    Int line contain data    
    int: in the String data
    string: in the int data 
    Int: is empty or no string 

I tried the below formula but its giving different results:

     =IF(ISNUMBER(SEARCH("string",A2)),"string",IF(ISNUMBER(SEARCH("int",A2)),"int",IF(ISNUMBER(SEARCH("int:",A2)),"int",IF(ISNUMBER(SEARCH("string:",A2)),"string"))))

Output given by the formula in B column:

    Template                         Template Name
    This is String                       String
    String line contains data            string
    This is Int                          int
    Int line contain data                int 
    int: in the String data              string    #it must be 'int'
    string: in the int data              int       #it must be 'string'
    Int: is empty or no string           string    #it must be 'int'

Please let me know what has to be changed to get the desired output.

Answer 1

Rearrange the search order: If you first search for the longer strings (with the ':') and then for the ones without ':', the results should be right.

=IF(ISNUMBER(SEARCH("string:",A2)),"string",IF(ISNUMBER(SEARCH("int:",A2)),"int",IF(ISNUMBER(SEARCH("int",A2)),"int",IF(ISNUMBER(SEARCH("string",A2)),"string"))))

Answer 2

edited: to make the code suitable for direct entry in the spreadsheet cell (I previuosly thought of a VBA macro)

if you want the first occurrence between "string" and "int", then use

=IF(IFERROR(SEARCH("int",RC1),10000)>IFERROR(SEARCH("string",RC1),10000),"String", "Int")"

should you handle the case where neither "string" nor "int" are in the string, then just advance a check over the occurrence of either one of them