Forum Discussion
TEXT EXTRACTION
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 "?".
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 Joe, this is indeed a Very useful info between search and find..
- Why?
- 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
Thanks Detlef_Lewin can you please share some formula with IFS or IFERROR
Thanks & Regards
Anupam Shrivastava