The set of text functions in Microsoft Excel are used to replace the text with the new text. These functions are widely used when deal with the data.
We will discuss usage of these functions in this Article.
The REPLACE formula
This function is used to replace part of string in a given string with a new string. We can also replace entire string with the new string.
The Syntax of the function is:
=REPLACE(text, start_index, num_chars, new_text)
Where text is the initial text given. start_index is the starting index from where we need to replace the text with new_text. num_chars is number of characters to replace.
Let’s take an example to see how this function will work.
| Formula | Result |
| =REPLACE(“Excel formula help”, 1, 5, “Microsoft Excel”) | Microsoft Excel formula help |
Observe that, the first word “Excel” in the given text “Excel formula help” was replaced with the new text “Microsoft Excel”.
The REPLACEB formula
Unlike REPLACE function, REPLACEB function works with bytes instead of characters. It considers 2-bytes for each character when Double Byte Character Set (DBCS) is selected as the default language in your System. Otherwise, this behaves like a REPLACE function.
The Syntax of the function is:
=REPLACEB(text, start_index, num_bytes, new_text)
Observe that, the Syntax is similar to the REPLACE function; except it considers number of bytes through it’s num_bytes arguments.
REPLACE and REPLACEB functions will replace the part of text from the given index. How about replacing the repeated words in the given text? Better way to do this through SUBSTITUTE function.
The SUBSTITUTE formula
This function is used to substitute the part of the text with the new text in the given text. 🙂 Confused? We just discussed about REPLACE functions; these are useful to replace part of the text in the given text. SUBSTITUTE function will do more than that; it replaces all the occurrences of the text in the given text with the new text.
The Syntax of the function is:
=SUBSTITUTE(text, old_text, new_text)
Where text is the given text. old_text is the text to be replaced or substitute with the new_text. When you use this, by default it substitutes all the occurrences of the old text with the new text.
| Formula | Result |
| =SUBSTITUTE(“123 123 123”, “1”, “!”) | !23 !23 !23 |
Observer that, all occurrences of “1” was replaced with “!”.
How about replacing specific occurrence, instead of all occurrences of the old text? SUBSTITUTE function has another variation, where it takes occurrence number as an argument to replace specific occurrence of the old text.
The Syntax of this variation of the function is:
=SUBSTITUTE(text, old_text, new_text, occurrence_num)
occurrence_num argument is optional. If you do not specify, this functions replaces all occurrences of the old text with new text. If we specify, only that occurrence of the old text will be replaced with the new text instead of replacing all occurrences.
| Formula | Result |
| =SUBSTITUTE(“123 123 123”, “1”, “!”, 2) | 123 !23 123 |
Note that, the above formula, replaced only the second occurrence of “1” with “!”.
We will discuss more topics in my upcoming Articles.
🙂 Sahida