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 Column "A"
has SDx70, then Column "B" is populated with SDX70.
Attached sheet.
Thanks in Advance,
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 "?".
Additional example to Mr. @Detlef Lewin suggestions, see attached file.
Are more suitable for older Excel versions.
If you use newer versions of Excel I would recommend Mr. Lewin's formulas.
=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.
