CODEWITHSUNDEEP
excel
Dragoon Shimizu
Dragoon Shimizu

Reputation: 171

How do I add/replace a value in column B if column A contains the searched word

I want to do the following using Excel :

In column AX it have a long description, and I want to find the text 5.0-Rated.png from it, and then add 5.0 out of 5.0 stars to column AY which currently have the values of 0.

I want to add that to the whole column.

Thanks.

Upvotes: 2

Views: 71

Answers (1)

CallumDA
CallumDA

Reputation: 12113

This should do the trick, place in AY1 and drag down as you like

=MID(AX1,SEARCH("?.?-rated.png",AX1),3)&" out of 5.0 stars"

Note: it will throw a #VALUE! error if there is no such filename in the string. You can avoid that by wrapping the whole formula in an IFERROR function like this:

=IFERROR(MID(AX1,SEARCH("?.?-rated.png",AX1),3)&" out of 5.0 stars","")

Working like so:
enter image description here

Upvotes: 1

Related Questions