Forum Discussion

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...

TEXT_EXTRACTION.xlsm9 KB

excel

Formulas and Functions

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

Iron Contributor

Jul 05, 2023

HiDetlef_Lewin , Can you please share the formula to achieve this using IF+FIND or IF+SEARCH option?

Br,

Anupam

JoeUser2004

Bronze Contributor

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
Iron Contributor
Jul 05, 2023
Thanks Joe, this is indeed a Very useful info between search and find..