CODEWITHSUNDEEP
excelconditional-statements
CRAIG
CRAIG

Reputation: 1029

Excel Formula, Replace Current Text If Keyword in Another field, Otherwise, Don't Replace The Current Text

I need an excel formula that will check another field to see if a particular word is there. If the word is there it will change the current field to something, but if the word is not there, it will leave the current field alone and not change it if it already has text in it.

I am using this formula, but it replaces the field with blank space instead of just leaving it alone. I need it to NOT replace the current text with anything unless it finds a match. 

 =IF(ISNUMBER(SEARCH("*credits*",G1)),"PayPal Customer","")

Thanks!

Craig

Upvotes: 1

Views: 1887

Answers (2)

Pekka
Pekka

Reputation: 3654

In Excel, a cell can contain either a value, or a formula.

To achieve the effect you want, place into G3

=IF(ISNUMBER(SEARCH("*credits*",G1)),"PayPal Customer",G2)

Then, place the original value into G2 (possibly hiding the column to reduce clutter) and then G3 will contain the required value.

Upvotes: 2

user2204167
user2204167

Reputation:

1st : You don't need wildcard characters '*' in SEARCH, it will give wrong result as use of this will return 1 (first character) if SEARCH succeeds.

2nd : You should use =ISERROR() and not =ISNUMBER() to check for error if search fails.

3rd: Your answer is :)

=IF(ISERROR(SEARCH("CREDITS",G1)),"","Pay pal customer")

Upvotes: 0

Related Questions