Search in Column and display in another column

[Per_]

Hello all,

I need some help with this: I got a column with strings, I would like to serach for the string LM in the column and display the strings that contain LM in another column.

Ex;

Column1
TT_LM
GG
YY
RR_LM

So I would like to have this in column2
TT_LM
RR_LM

please help!
Per

[benishiryo]

welcome to the forum, Per. if you're trying to find those ending with "LM", try this in C1:
=IFERROR(INDEX($A$1:$A$10,SMALL(IF(RIGHT($A$1:$A$10,2)="LM",ROW($A$1:$A$10)),ROWS(C$1:C1))-ROW($A$1)+1),"")

if you're trying to find those containing "LM", then:
=IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER(SEARCH("LM",$A$1:$A$10)),ROW($A$1:$A$10)),ROWS(C$1:C1))-ROW($A$1)+1),"")

both are array formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Per_]

Thanks!

I do not manage to get it in

I pase the formula and after that I press CTRL+SHIFT+ENTER, is that correct ? But the formula does not "activate"

[XOR LX]

Hi,

Have you really given up so quickly on this solution that you have to post on another forum?

http://www.mrexcel.com/forum/excel-q...er-column.html

Would it not be worth investigating/checking that you have entered the array formula correctly before doing so? Would seem like a shame (and a touch unfair on benishiryo, perhaps) if you didn't first confirm whether the solution offered here, when correctly entered, worked or not, before seeking help on another forum.

Regards