CODEWITHSUNDEEP
excel
Ryan_C
Ryan_C

Reputation: 501

Using Excel "IF" Statment To Filter Out Text Beginning Or Ending With A Certain Character

Working on the same project as before and I need to find a way to to write an "IF" statement that will search for words beginning with a "<" as well as words that end with a " \ ". I would like to conditionally highlight those cells with the value "IGNORE: HIDDEN FIELD".

I searched around and found that there is a SEARCH function in Excel but that doesn't seem to be what I'm looking for.

Is anyone aware of how to do this? "If a cell begins with a "<" or ends with a " \ " then blah blah blah".

Upvotes: 9

Views: 49446

Answers (4)

Tomasz Klaszczyk
Tomasz Klaszczyk

Reputation: 11

You could do the same job without IF

Replace function (CTRL+F) and "*" operator.

Press CTRL+F

Type "<*" to look for values that cells witch content begins with< Than replace for whatever you want.

Type "*/" to find cells that's values end with / Than replace it for whatever you want.

Or even better you could use Conditional Formating, it has one option for highlighting the cells that begins with a certain character.

Upvotes: 0

user4039065
user4039065

Reputation:

Try,

=IF(OR(LEFT(A1)={"<","\"}),"blah-blah-blah", "")

That formula covers cell values beginning with either a less than or backslash. If the backslash should be checked at the end of the cell value then this would be more appropriate.

=IF(OR(LEFT(A1)="<",RIGHT(A1)="\"),"blah-blah-blah", "")

The LEFT() and RIGHT() functions default to a single character so if only the first character from either direction is to be examined the num_chars parameter is not required.

Upvotes: 2

seriousdk
seriousdk

Reputation: 19

Looks like you can use SEARCH()

=IF(OR(SEARCH("\";A1;1)=LEN(A1);SEARCH("<";A1;1)=1); "blahblahblah";"blahblahblah")

Upvotes: 1

Bathsheba
Bathsheba

Reputation: 234665

To match words that start with < and end with \, use

=IF(AND(LEFT(A1,1)="<",RIGHT(A1,1)="\"),TRUE,FALSE)

To match words that start with < and / or end with \, use

=IF(OR(LEFT(A1,1)="<",RIGHT(A1,1)="\"),TRUE,FALSE)

Cell A1 contains the word to test. This can form the basis of your conditional formatting test. If you want to display alternative text then replace TRUE and FALSE to suit personal taste, e.g. IGNORE: HIDDEN FIELD and A1 respectively.

Upvotes: 10

Related Questions