SOLVED

# TEXT EXTRACTION

Brass Contributor

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

Br.

Anupam

11 Replies

# Re: TEXT EXTRACTION

``=LOOKUP(9^9,SEARCH({"SDX65","SDX70"},A2),{"SDX65","SDX70"})``

# Re: TEXT EXTRACTION

@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

# Re: TEXT EXTRACTION

It is just a big number. Bigger than the maximum textlength in your data.

Maybe 999 could be enough.

# Re: TEXT EXTRACTION

Thanks @Detlef Lewin can you please share some formula with IFS or IFERROR

Thanks & Regards

Anupam Shrivastava

# Re: TEXT EXTRACTION

Hi@Detlef Lewin , Can you please share the formula to achieve this using IF+FIND or IF+SEARCH option?

Br,

Anupam

Why?

# Re: TEXT EXTRACTION

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

# Re: TEXT EXTRACTION

``=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().

best response confirmed by anupambit1797 (Brass Contributor)
Solution

# Re: 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 "?".

# Re: TEXT EXTRACTION

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.

# Re: TEXT EXTRACTION

Thanks Joe, this is indeed a Very useful info between search and find..