SEARCH()
SEARCH will only provide the character position if it finds one. If search-string is found it will give position number else it will give -1
Syntax: SEARCH (String, Search-string)
Example: If you want to find A from the given string
Result:
A common use of SEARCH is to extract the file name from an attachment in a rows using a formula
REPLACE() and SUBSTITUTE()
The REPLACE and SUBSTITUTE functions accomplish very similar but different tasks.
You use REPLACE() to replace a single instance of some amount of text (e.g. a name) and you use SUBSTITUTE when you want to replace them all.
Format: REPLACE (String, Start Position, End Position, ReplaceString)
Example:
Result: Good Afternoon
SUBSTITUTE() will substitute value with new value. SUBSTITUTE will successfully replace every instance of happy with nice in every row using that text field. If it finds nothing, it will replace nothing and leave the text as-is.
Example:
Result: Wish him nice day.