TEXT EXTRACTION

[anupambit1797]

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

[#2]

@anupambit1797 

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

[#3]

@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

[#4]

@anupambit1797 

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

Maybe 999 could be enough.

[#5]

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

Thanks & Regards

Anupam Shrivastava

[#6]

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

Br,

Anupam

[#7]

Why?

[#8]

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

[#9]

@anupambit1797 

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

[#10 · Accepted solution]

@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 "?".

[#11]

@anupambit1797 

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.

[#12]

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