Search for a word in a cell and replace the whole cell

[Co Don]

All,

Appreciate for the all the help:

How can I search for a specific word in the Cell's content if found, then replace the whole cell's content with this specific word. If not found, no change.

for example:
If(A2 = "MLB"), then replace A2's content with just MLB, if false, do nothing).

I tried playing around with Lookup, Vlookup and If function, but not quite get the answer....

Thanks you very much....

Co Don.

 

[Scott Huish]

You can't change another cell's value with a formula.

But perhaps:
Select Column A.
Click Edit, then Find and Replace (or CTRL-H)
After Find What, put *MLB*
After Replace With, put MLB
Click on Replace All

or as a macro:

Sub ChangeMLB()
Range("A:A").Replace What:="*MLB*", Replacement:="MLB", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[Co Don]

Thanks....

I tried the first option you suggested (Find and Replace), but I didn't put * in front of and after the MLB.....

But now with your suggestion, it works!!!

Many thanks.

Co Don