Here, we will learn how to use the Excel text functions: FIND, SEARCH, REPLACE and SUBSTITUTE.
Excel FIND function
The Excel FIND function returns the numeric position of the given text string inside another given text string. For example, the formula =FIND("App","Apple") returns 1. The return value of the function is always the number representing the location of the given substring. FIND is case sensitive so, the formula =FIND("app","Apple") returns the #VALUE! error. The function returns the same error when the given substring is not found. FIND does not support wildcards like *?~, and doing so will force the function to return the same error. The formula in C4, copied down, is:
=FIND(A4,B4)
Excel SEARCH function
The Excel SEARCH function returns the numeric position of the given text string inside another given text string. For example, the formula =SEARCH("App","Apple") returns 1. The return value of the function is always the number representing the location of the given substring. SEARCH is not case sensitive so, the formula =SEARCH("app","Apple") still returns 1. The function returns the #VALUE! error when the given substring is not found. SEARCH does support wildcards like *?~. So, you can use the formula like: =SEARCH("*ell","Hello") that returns 1. The formula in G4, copied down, is:
=SEARCH(E4,F4)
Excel REPLACE function
The Excel REPLACE function replaces characters in the given string with another string, specified by location. For example, the formula =REPLACE("HD",1,2,"High Definition") returns "High Definition". In the given formula, "HD" is the text to replace, 1 is the starting location to replace, 2 is the number of characters to replace and "High Definition" is the text to replace "HD" with. The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with REPLACE if you are working with numeric values, like this: =VALUE(REPLACE(2024,4,1,5)). The formula in J4, copied down, is:
=REPLACE(I4,8,1,5)
Excel SUBSTITUTE function
The Excel SUBSTITUTE function replaces characters in the given string with another string, by matching. For example, the formula =SUBSTITUTE("apple","app","batt") returns "battle". In the formula (from left to right), "apple" is the text for which we want to substitute characters, "app" is the text to replace and "batt" is the new text to replace "app" with. The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with SUBSTITUTE if you are working with numeric values, like this: =VALUE(SUBSTITUTE(2024,4,5)).
You have successfully learnt how to use the Excel text functions: FIND, SEARCH, REPLACE and SUBSTITUTE.
