How to check for a value of ** in IF() in MS Excel?
Solution 1:
As stated here:
If the value of find_text is not found, the #VALUE! error value is returned
When the * is not present in the text, you get #VALUE! because the * is not found. Change your formula to:
=IF(ISERROR(SEARCH("~*",A4)),"**"&A4&"**",A4)
ISERROR function will return TRUE if an error is found (in your case #VALUE). So the whole formula adds ** if there is an error. If not, it returns the cell's value.