How to build a specific type of argument for the IF function

[brunocinelli]

Hi all,

I'm currently facing the following problem:

I have a matrix with one column and 10,000+ lines; all cells contain a text string, which contains the characters "Lx", x being the an integer between 1 and 9 (i.e Line1 = TEXT (L1); Line2 = TEXT2 (L3); Line3 = TEXT3 (L2). and so on).

What I'm trying to do is simply copy the content of every cell containing ¨"L6" in the cell immediately next to it (the cells are on column B, and I'd like to copy the ones containing "L6" in column A).

What's the best way to do this? I was thinking of a macro, but I don't know how to insert "contains "L6"" into the argument of the IF function I'd use in that macro.

Many thanks in advance for your help,
Bruno

[etaf]

copy to where - if column C
put in C2 and copy down

IF( A2="L6", B2, "")

[brunocinelli]

but the cells also contain other text strings, not only "L6"; so the argument can't be A2="L6"...

The cells are like Total Direct Expenses (L6), Total Revenues (L1), and so forth

[etaf]

OK
Can L6 appear in the other text in the string for any reason at all

try
=IF(ISERROR(SEARCH("*L6*",A2,1)),"",B2)

[brunocinelli]

thanks a lot! this solves the problem.

could you also post how to do it with a macro, though?

appreciate your help!

[etaf]

No , sorry, my macros are from version 97 and thats the last time i used - so very very rusty now - so will wait for another member to reply to that