14,534
As the title of this article suggests these are Excel Worksheet functions for use in cell formula as opposed to VBA functions. Although some may also be available in VBA they are not as a rule. Where the function is available in VBA I will state it.
SEARCH()
The SEARCH function is used to "search" text for a given substring. It is not case sensitive. If you need something that is case sensitive then use the SUBSTITUTE function instead. The SEARCH function can be very useful if you need to search a column of text for a particular reference to something.
For example, if you have a column of addresses and wish to search for all those addresses in a particular town, then you can search for the town as a substring of the text in the address column.
The syntax for the function is:
SEARCH (substring, Cell Reference of Text, [start_position] )Note: start_position is optional. If no value is entered it defaults to 1, the first character of the Body of Text.
In this first image you can see the formulas I used to test the function. In Cell Reference A1 I inserted a block of text (In this case an address). In Cell Reference A2 I entered the SEARCH Function and inserted the substring I want to search for and passed it the Value in A1 to search.
In the below image you can see the values when I turn off the Formula display. You will note that it returns the position of the text when it is found (which can be useful) and #VALUE error if the substring is not found. If you need to return a value such as 0 if the substring is not found you can use the ISERROR function to evaluate the result.
SUBSTITUTE()
The SUBSTITUTE function is used to do substitute one set of characters for another. There are many real world examples where this would be useful but I am using a simple one to explain.
The syntax for the function is:
SUBSTITUTE ( cell reference, oldText, newText, [nth time oldText appears] )Note: [nth time oldText appears] is optional. It allows you to designate only replacing a specific appearance of the old text. If no value is entered for this all appearances of the old text will be substituted with the new text.
In this first image you can see the formulas I used to test the function. The text I am evaluating is in column A. In this case I am telling it what position in the text the characters I wish to replace appear and how many characters to remove from the text before inserting the new text.
In the below image you can see the values when I turn off the Formula display.
REPLACE() ... VBA has a similar function but it has different syntax
The REPLACE function replaces a sequence of characters in a string with another set of characters. Unlike the SUBSTITUTE function you are not giving an old set of characters to replace but rather the position (start position) in a block of text and the number of characters in the block you wish to replace.
The syntax for the function is:
REPLACE ( Cell Reference, Start_position, number of characters, newText )In this first image you can see the formulas I used to test the function. The text I am evaluating is in column A.
In the below image you can see the values when I turn off the Formula display.
Mary McCarthy
Bytes Admin
