Excel copy cell from a coloumn if value is starting with Formula-CodePudding

I have this kind of column in my excel -

A              B
gbgdf,  
dsfsd,
gdgdf,
TC-EVTgfdsfs,
gdfgd,

I am willing to copy every cell that is NOT starting with 'TC-EVT'

I tried to use different formulas, such as:

LOOKUP
SWITCH

But with no success at all.

I need column B to look like this -

gbgdf,  
dsfsd,
gdgdf,
gdfgd,

Thank you!

CodePudding user response：

Try this formula, assuming you are using either O365 or Excel 2021

• Formula used in cell B1

=FILTER(A1:A5,LEFT(A1:A5,6)<>"TC-EVT")

Or,

• Formula used in cell C1

=FILTER(A1:A5,NOT(LEFT(A1:A5,6)="TC-EVT"))

CodePudding user response：

THE ANSWER I FOUND TO BE PERFECTLY WORK:

=FILTER(A1:A5,NOT(LEFT(A1:A5,6)="TC-EVT"))