Forum Discussion
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...
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 "?".
=LOOKUP(9^9,SEARCH({"SDX65","SDX70"},A2),{"SDX65","SDX70"})
Detlef_LewinThanks for your response, could you please educate me on this value 9^9, ?why we used this 9 power 9 here?
Thanks and if any other formula like INDEX-MATCH , if could be used?
Br,
Anupam
It is just a big number. Bigger than the maximum textlength in your data.
Maybe 999 could be enough.
HiDetlef_Lewin , Can you please share the formula to achieve this using IF+FIND or IF+SEARCH option?
Br,
Anupam
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 "?".
Thanks Detlef_Lewin can you please share some formula with IFS or IFERROR
Thanks & Regards
Anupam Shrivastava