Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 29, 2023
Solved

TEXT EXTRACTION

Dear Experts,                      Could you please share on how can we populate column="B" with SDX65/70, so criteria is if Column "A" contains SDX65, column"B" is populated with SDX65, and if Col...
excel
Formulas and Functions
  • JoeUser2004's avatar
    JoeUser2004
    Jul 05, 2023

    anupambit1797 

     

    Is this what you are looking for?

     

    =IF(ISNUMBER(SEARCH("SDX65",A2)), "SDX65", IF(ISNUMBER(SEARCH("SDX70",A2)), "SDX70", ""))

     

    Replace SEARCH with FIND, especially if you want a case-sensitive search.

     

    -----

    Alternatively:

     

    =IFERROR(MID(A2,SEARCH("SDX??",A2),5), "")

     

    returns any 5-character string that begins with "SDX" (or "sdx" or any combination of mixed case).

     

    Note that we cannot use FIND (for a case-sensitive search) here because FIND does not allow wildcard characters like "?".

     

anupambit1797's avatar
anupambit1797
Iron Contributor
Jul 05, 2023
Because, from my understanding , solving problems with more than one method is always useful to explore, Solution is not the only last thing , on how many ways we can explore to solve it seems more important..

anyways I tried to use the IF there and seems some minor thing I am missing , will check this more..

Thanks & Regards
Anupam Shrivastava
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 05, 2023

    anupambit1797 

    =CONCAT(IF(ISNUMBER(SEARCH({"SDX65","SDX70"},A2)),{"SDX65","SDX70"},""))

    That's 4 functions.

    The LOOKUP solution only needs 2 functions.

     

    Now you try with AVERAGE().

     

Resources