How to automatically replace the text in a cell if it contains a specific word?

%3CLINGO-SUB%20id%3D%22lingo-sub-2188709%22%20slang%3D%22en-US%22%3EHow%20to%20automatically%20replace%20the%20text%20in%20a%20cell%20if%20it%20contains%20a%20specific%20word%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2188709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Web%20capture_6-3-2021_03940_www.udemy.com.jpeg%22%20style%3D%22width%3A%20707px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261277iFF3C7A36A94C956B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Web%20capture_6-3-2021_03940_www.udemy.com.jpeg%22%20alt%3D%22Web%20capture_6-3-2021_03940_www.udemy.com.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHello%20everyone!%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20the%20above%20image%2C%20Which%20function%20can%20I%20use%20to%20replace%20the%20text%20in%20a%20cell%20that%20contains%20a%20specific%20word%20with%20another%20word.%20I%20have%20text%20of%20this%20kind%20%22Yes%3A%2005.03.2021%2014%3A03%22%20%26nbsp%3B(all%20the%20cells%20are%20different)%2C%20and%20I%20need%20to%20replace%20it%20with%20just%20%22Yes%22.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20it%20possible%20to%20make%20it%20quick%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2188709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2188779%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20replace%20the%20text%20in%20a%20cell%20if%20it%20contains%20a%20specific%20word%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2188779%22%20slang%3D%22en-US%22%3EI%20found%20the%20solution.%3CBR%20%2F%3E%3DLEFT(B2%2CFIND(%22%3A%22%2CB2)-1)%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20it%20correct%3F%3C%2FLINGO-BODY%3E
Regular Contributor

Web capture_6-3-2021_03940_www.udemy.com.jpeg

 

Hello everyone!

In the above image, Which function can I use to replace the text in a cell that contains a specific word with another word. I have text of this kind "Yes: 05.03.2021 14:03"  (all the cells are different), and I need to replace it with just "Yes".

 

Please help..???

3 Replies
I found the solution.
=LEFT(B2,FIND(":",B2)-1)

Is it correct?
Yes!
Used and tested AOK assuming your version of Excel uses the comma argument separator.
If in EU or Indonesia use:
=LEFT(A6;FIND(":";A6)-1)
HopeThisHelps