Geek Answers Handbook

Using Excel "IF" Statment to filter text starting or ending with a specific character

I’m working on the same project as before, and I need to find a way to write an “IF” statement that will look for words starting with "<" as well as words that end with "\". I would like to conditionally select these cells with the value "IGNORE: HIDDEN FIELD".

I searched and found that Excel has a SEARCH function, but that doesn't look like what I'm looking for.

Does anyone know how to do this? "If the cell begins with" <"or ends with the character" \ ", then blah blah blah".

+7
excel
source share
4 answers

To combine words starting with < and using \ , use

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

To combine words starting with < and / or using \ , use

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

Cell A1 contains the word to check. This may be the basis of your conditional formatting test. If you want to display alternate text, replace TRUE and FALSE according to your personal taste, for example. IGNORE: HIDDEN FIELD and A1 respectively.

+7
source share

Try

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

This formula covers cell values ​​starting with a smaller or backslash. If the backslash needs to be checked at the end of the cell value, this would be more appropriate.

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

The LEFT() and RIGHT() functions are equal by default to one character, therefore, if you need to examine only the first character from any direction, the num_chars parameter is not required.

+2
source share

It looks like you can use SEARCH()

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

+1
source share

You can complete the same task without IF

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

Press CTRL + F

Enter " <* " to find the values ​​whose contents begin with < Then replace whatever you want.

Enter " */ " to find cells whose values ​​end with / Then replace it with whatever you want.

Or it's even better to use Conditional Formating, it has one option for selecting cells starting with a specific character.

0
source share

More articles:


All Articles