CODEWITHSUNDEEP
excelvba
Lobo de Colorado
Lobo de Colorado

Reputation: 307

EXCEL: Find and Replace, Change Entire Cell Value

I want to :

  1. Search if cell contains a certain text (like "orange" in "orange crush")
  2. If return true, change entire cell value to "orange"

Up until now, I can search for the cell, and change the specific text to something I want, but not the entire cell (like "orange" to "blue" and have it be "blue crush", which isn't what I want to do. I want it just to be "blue"). I've been doing this with the "find & replace" function.

I've also used the function 

=if(isnumber(search("orange",B2)), substitute(B2, B2, "orange"), B2)

which works!, but prints in the new column I wrote the code, and doesn't replace the value in B2 where it's referencing.

Possibly done in VBA?

What I have:

Oranges
Orange slices
Orange
Orange pulp

What I want:

Orange
Orange
Orange
Orange

Upvotes: 3

Views: 21749

Answers (2)

chainstair
chainstair

Reputation: 817

It is much easier to achieve that:

Excel -> Open Find and Replace

Find what: *orange*

(*-is wildcard for ignore everything before and after "orange". Finds and marks whole cell. Example: Finds cell "small orange shake")

Replace with: orange

enter image description here

Upvotes: 7

Grant Miller
Grant Miller

Reputation: 29047

The best you can do with a formula is:

=IF(ISNUMBER(SEARCH("Orange", A1)), "Orange", A1)

If you wanted a case-insensitive search, then use:

=IF(ISNUMBER(SEARCH(LOWER("Orange"), LOWER(A1))), "Orange", A1)

Then, if you wanted to overwrite the original column, make sure you paste the values over the original column:

CTRL + ALT + V

Upvotes: 3

Related Questions