Excel REPLACE Function

What is the REPLACE function in Excel?


The REPLACE function is one of the TEXT functions of Excel.

It replaces part of a text string with a different text string.

We can find this function in TEXT category of insert function Tab.

How to use REPLACE function in excel

  1. Click on an empty cell (like F5).
empty cell in excel

2. Click on the fx icon (or press shift+F3).

fx icon in excel

3. In the insert function tab you will see all functions.

insert function tab in excel

4. Select TEXT category.

5. Select REPLACE function

6. Then select ok.

excel  REPLACE function

7. In the function arguments Tab you will see REPLACE function.

8. Old text is text in which you want to replace some characters.

9. Start num is the position of the character in Old text that you want to replace with New text.

10. Num_chars is the number of characters in Old text that you want to replace.

11. New text is the text that will replace characters in Old text.

12. You will see the results in formula result section.

Examples of REPLACE function in Excel

Sure, here are 10 examples of the REPLACE function in Excel:

  1. Replace all instances of a certain character in a cell: =REPLACE(A2,3,1,”X”) – This formula replaces the third character in the text in cell A2 with the letter “X”.
  2. Remove a certain number of characters from the beginning of a string: =REPLACE(A2,1,4,””) – This formula removes the first four characters from the text in cell A2.
  3. Replace specific text within a cell: =REPLACE(A2,FIND(“old”,A2),3,”new”) – This formula finds the location of the word “old” in cell A2, and replaces it with the word “new”.
  4. Replace multiple instances of a character or string: =REPLACE(A2,3,2,”123″) – This formula replaces two characters starting at the third position in cell A2 with the string “123”.
  5. Replace a certain number of characters from the end of a string: =REPLACE(A2,LEN(A2)-3,4,””) – This formula removes the last four characters from the text in cell A2.
  6. Replace text based on a specific condition: =IF(LEN(A2)>15,REPLACE(A2,16,LEN(A2)-15,”…”),A2) – This formula checks if the text in cell A2 is longer than 15 characters, and if so, replaces the remaining characters with an ellipsis (“…”).
  7. Replace only the first instance of a character in a cell: =REPLACE(A2,FIND(“,”,A2),1,”.”) – This formula finds the first comma in cell A2 and replaces it with a period.
  8. Replace text in a range of cells: =REPLACE(B2:B10,3,1,”X”) – This formula replaces the third character in each cell in the range B2:B10 with the letter “X”.
  9. Replace non-Latin characters: =REPLACE(A2,FIND(“日”,A2),1,”Sun”) – This formula finds the first occurrence of the Japanese character for “day” in cell A2 and replaces it with the word “Sun”.
  10. Replace text based on a lookup table: =REPLACE(A2,FIND($D2:2:D5,A2),LEN(5,A2),LEN(D2:2:D5),5),E2:2:E$5) – This formula replaces any text in cell A2 that matches a value in the lookup table in columns D and E with the corresponding replacement value from column E.

What is the purpose of REPLACE function?

It replaces part of a text string with a different text string.

What is the Return value  of REPLACE function?

It just returns the number.

SEARCH (Find text,Within text,Start num)=number

How many arguments does REPLACE function have?

SEARCH (Old text,Start num,Num_chars,New text)

REPLACE function has 4 argument.

Old text is text in which you want to replace some characters

Start num is the position of the character in Old text that you want to replace with New text

Num_chars is the number of characters in Old text that you want to replace

New text is the text that will replace characters in Old text

Which version of excel supports REPLACE function?

This function is available for all excel versions (2003-2019)

What are the arguments for the REPLACE function?

The REPLACE function is a text function that allows you to replace a specified number of characters within a text string with new text. The syntax for the REPLACE function is as follows:

=REPLACE(old_text, start_num, num_chars, new_text)

Here’s what each argument means:

  1. old_text: This is the existing text string that you want to modify.
  2. start_num: This is the position within the old_text where you want to begin replacing characters. For example, if you want to replace the third character of the text string, you would set start_num equal to 3.
  3. num_chars: This is the number of characters you want to replace in old_text, starting at start_num. For example, if you want to replace three characters in the text string, you would set num_chars equal to 3.
  4. new_text: This is the new text string that you want to replace the specified characters with.

Now, let’s take a look at some examples to see how the REPLACE function works in practice:

Example 1: Replace all instances of a certain character in a cell

Suppose we have the text “banana” in cell A2, and we want to replace all instances of the letter “a” with the letter “o”. We can use the following formula:

=REPLACE(A2, FIND(“a”,A2), LEN(“a”), “o”)

This formula finds the position of the first occurrence of the letter “a” in cell A2 using the FIND function. It then replaces every occurrence of “a” in the cell by specifying LEN(“a”) as the num_chars argument.

Example 2: Remove a certain number of characters from the beginning of a string

Suppose we have the text “Hello World” in cell A2, and we want to remove the first five characters from the beginning of the string. We can use the following formula:

=REPLACE(A2, 1, 5, “”)

This formula starts replacing characters from the first position (start_num = 1) and replaces the first five characters (num_chars = 5) with a blank string, effectively removing them from the original text.

Example 3: Replace specific text within a cell

Suppose we have the text “This is a test” in cell A2, and we want to replace the word “test” with the word “example”. We can use the following formula:

=REPLACE(A2, FIND(“test”,A2), LEN(“test”), “example”)

This formula finds the position of the word “test” in cell A2 using the FIND function, and then replaces it with the word “example”.

These are just a few examples of how you can use the REPLACE function in Excel. By using different combinations of arguments, you can customize the function to fit your specific data cleaning needs.

Can the REPLACE function be used to replace multiple instances of a character or string in a cell or range?

Yes, the REPLACE function in Excel can be used to replace multiple instances of a character or string in a cell or range.

To replace multiple instances of a character or string in a cell, you can use the FIND and REPLACE functions together. The FIND function will identify the position of each instance of the character or string, and then the REPLACE function will replace each instance with the desired new text. Here’s an example:

Suppose we have the text “The rain in Spain falls mainly on the plain” in cell A1, and we want to replace every occurrence of the word “rain” with the word “sunshine”. We can use the following formula:

=REPLACE(A1,FIND(“rain”,A1),LEN(“rain”),”sunshine”)

This formula uses the FIND function to find the position of each occurrence of the word “rain” in the text string, starting from the beginning of the string. It then replaces each occurrence of “rain” with “sunshine”.

To replace multiple instances of a character or string in a range of cells, you can use the REPLACE function with array formulas. Here’s an example:

Suppose we have a range of cells A1:A5, each containing a text string that includes the word “apple”. We want to replace all occurrences of “apple” with “orange” in this range. We can use the following formula:

{=REPLACE(A1:A5,FIND(“apple”,A1:A5),LEN(“apple”),”orange”)}

Note that this is an array formula, so you will need to press Ctrl + Shift + Enter instead of just Enter when entering it into the formula bar. This formula uses the FIND function to find the position of each occurrence of “apple” in each cell in the range, and then replaces each occurrence with “orange”.

In summary, the REPLACE function in Excel can indeed be used to replace multiple instances of a character or string in a cell or range. The specific formula and function combination used will depend on the data being worked with, but the basic principle is to use FIND to identify the positions of each instance, and then use REPLACE to replace each one with the desired new text.

Can the REPLACE function be used to replace text based on specific criteria?

Yes, the REPLACE function in Excel can be used to replace text based on specific criteria using logical functions like IF and SEARCH.

For example, suppose we have a list of products in column A of an Excel worksheet, and we want to replace all instances of the word “apple” with “orange” for products that are fruits. We can use the following formula:

=IF(SEARCH(“fruit”,A1)>0,REPLACE(A1,SEARCH(“apple”,A1),LEN(“apple”),”orange”),A1)

This formula first checks if the word “fruit” appears in the product name using the SEARCH function. If it does, the formula uses the REPLACE function to replace each occurrence of “apple” with “orange”. If not, the formula simply returns the original product name.

Here’s how this formula works in more detail:

  • SEARCH(“fruit”,A1) looks for the word “fruit” within the text string in cell A1. If “fruit” is found, the SEARCH function returns the position of the first character where it appears. If “fruit” is not found, the SEARCH function returns the #VALUE! error.
  • IF(SEARCH(“fruit”,A1)>0,…) checks if the SEARCH function returns a value greater than 0 (i.e., whether “fruit” is found in the product name). If it does, the formula proceeds to the next part of the formula. If not, the formula returns the original product name using the A1 argument at the end of the formula.
  • REPLACE(A1,SEARCH(“apple”,A1),LEN(“apple”),”orange”) replaces each occurrence of “apple” in the product name (which is represented by A1) with “orange”. The FIND function finds the position of the first instance of “apple” in the text string, and LEN calculates the length of “apple”. This information is then used as input to the REPLACE function.
  • The entire formula is copied down the column to apply the same logic to all of the products in the list.

By using logical functions like IF and SEARCH in combination with the REPLACE function, you can replace text based on specific criteria in Excel.

How can I use the REPLACE function to remove certain characters from a cell or range?

You can use the REPLACE function in Excel to remove certain characters from a cell or range by replacing them with an empty string (“”). Here’s how you can do it:

Suppose we have a range of cells A1:A5 that contain text strings with unwanted characters, such as hyphens (“-“) or brackets (“()”). We want to remove these characters from the text strings. We can use the following formula:

=REPLACE(A1, FIND(“-“,A1), 1, “”)

This formula uses the FIND function to locate the position of the first hyphen in cell A1. The REPLACE function is then used to replace the hyphen with an empty string (“”), effectively removing it from the original text. This formula can be copied and pasted down the range A1:A5 to remove all occurrences of the hyphen.

If you want to remove multiple characters at once, you can chain together multiple REPLACE functions within the same formula. For example, suppose we have a range of cells A1:A5 that contain text strings with both hyphens and brackets. We want to remove both of these characters from the text strings. We can use the following formula:

=REPLACE(REPLACE(A1, FIND(“-“,A1), 1, “”), FIND(“(“,A1), 1, “”)

This formula uses two separate REPLACE functions within one larger formula. The first REPLACE function removes any hyphens from the text string in cell A1. The second REPLACE function then removes any opening brackets (represented by “(“) from the resulting text string. Again, this formula can be copied and pasted down the range A1:A5 to remove all occurrences of both characters.

In summary, by using the REPLACE function in combination with the FIND function, you can remove unwanted characters from a cell or range of cells in Excel. Just specify the character you want to remove in the FIND function, and replace it with an empty string using the REPLACE function.

Are there any limitations to using the REPLACE function?

While the REPLACE function in Excel is a versatile tool for modifying text strings, there are some limitations to be aware of.

  1. Case-sensitivity: By default, the REPLACE function is case-sensitive. This means that if you’re trying to replace text but your search criteria doesn’t match the exact case of the text in the cell, the function won’t work as expected. To avoid this problem, you can use functions like LOWER or UPPER to standardize the case of the text before running the REPLACE function.
  2. Exact match requirement: In order for the REPLACE function to work correctly, you need to provide exact match criteria. If the text you want to replace appears in multiple formats (e.g., with or without punctuation), you’ll need to use additional functions to account for these variations.
  3. Limited to replacing within one cell: The REPLACE function can only replace text within a single cell at a time. If you want to replace text across multiple cells or an entire range of cells, you’ll need to use formulas that reference multiple cells and/or use array formulas.
  4. Order of arguments matters: When using the REPLACE function, it’s important to pay attention to the order in which you specify the arguments. Specifically, the start_num argument needs to come before the num_chars argument, or the function won’t work correctly.
  5. Not suitable for complex string manipulation: While the REPLACE function is useful for simple text modifications, it may not be sufficient for more complex tasks. Functions like REGEXREPLACE or VBA code might be required for more advanced string manipulation.

In summary, while the REPLACE function is a powerful tool for working with text strings in Excel, there are some limitations to be aware of. By paying attention to these limitations, you can avoid common pitfalls and make the most of this useful function.

Can the REPLACE function be combined with other Excel functions?

Yes, the REPLACE function in Excel can be combined with other Excel functions to perform more complex text manipulations.

Here are a few examples:

  1. LEFT and RIGHT functions: You can use the LEFT and RIGHT functions in combination with the REPLACE function to modify only certain portions of a text string. For example, if you have a list of product codes in column A that are all 10 characters long, and you want to remove the last two characters from each code, you can use the following formula:

=LEFT(A1,8)

This formula uses the LEFT function to extract the first eight characters of the text string, effectively removing the last two characters. This formula can be copied down the entire column to apply this logic to all the cells in the column.

  1. CONCATENATE function: You can use the CONCATENATE function in combination with the REPLACE function to join multiple text strings together after making replacements. For example, suppose you have three cells, A1, B1, and C1, each containing a different part of a mailing address. You want to remove any commas from each cell and then combine all three cells into one complete mailing address. You can use the following formula:

=CONCATENATE(REPLACE(A1,FIND(“,”,A1),1,””), ” “, REPLACE(B1,FIND(“,”,B1),1,””), ” “, REPLACE(C1,FIND(“,”,C1),1,””))

This formula uses the REPLACE function to remove any commas from each cell, and then concatenates the three modified text strings together using the CONCATENATE function. Note that spaces are included within the CONCATENATE function to ensure that the resulting text string is properly formatted as an address.

  1. IF function: You can use the IF function in combination with the REPLACE function to apply text modifications selectively based on certain criteria. For example, suppose you have a range of cells A1:A5 that contain product names, and you want to add the word “Sale” to the beginning of any product name that includes the word “Discount”. You can use the following formula:

=IF(ISNUMBER(SEARCH(“Discount”,A1)),REPLACE(A1,1,0,”Sale “),A1)

This formula uses the SEARCH function to identify whether the word “Discount” appears in each cell in the range. If it does, the REPLACE function is used to add the word “Sale” to the beginning of the text string. If not, the original text string is returned.

In summary, there are many Excel functions that can be used in combination with the REPLACE function to perform more complex text manipulations. By understanding how these functions work together, you can create powerful formulas for working with text strings in Excel.

Does the REPLACE function work with non-Latin characters?

Yes, the REPLACE function in Excel works with non-Latin characters. The function is designed to work with any character set or language that Excel supports.

For example, suppose we have a range of cells A1:A5 that contain text strings in Japanese. We want to replace all occurrences of the Japanese character “あ” (pronounced “a”) with the character “い” (pronounced “i”). We can use the following formula:

=REPLACE(A1,FIND(“あ”,A1),1,”い”)

This formula uses the FIND function to locate the position of the first occurrence of the character “あ” in cell A1. The REPLACE function is then used to replace the character with the desired character “い”. This formula can be copied and pasted down the range A1:A5 to replace all occurrences of the character.

Similarly, the REPLACE function can be used with other non-Latin characters or character sets, such as Cyrillic, Chinese, or Arabic. As long as the character set is supported by Excel, the REPLACE function can be used to modify text strings that use those characters.

It’s worth noting, however, that some non-Latin character sets or fonts may not display correctly in Excel if the proper language packs or character sets are not installed on your computer. In such cases, it may be necessary to install additional language packs or fonts to ensure that the characters are displayed correctly and that the REPLACE function works as expected.

In summary, the REPLACE function in Excel works with any character set or language that is supported by Excel, including non-Latin characters. It provides a powerful tool for modifying text strings regardless of their character set or language.

Can I use the REPLACE function to modify large amounts of text data at once?

Yes, you can use the REPLACE function in Excel to modify large amounts of text data at once. However, there are a few considerations to keep in mind when working with large volumes of data.

  1. Performance: While the REPLACE function is a powerful tool for modifying text, it can be slow when working on large amounts of data. If you’re working with very large datasets, it may be necessary to split up the data into smaller chunks and apply the function to each chunk separately.
  2. Memory usage: Working with large amounts of data can also impact your computer’s memory usage. It’s important to close any other applications or files that you don’t need while working with large datasets to minimize memory usage and optimize performance.
  3. Testing: Before applying the REPLACE function to a large dataset, it’s always a good idea to test it on a smaller sample first. This will help you identify any errors or issues before they affect the entire dataset.
  4. Backing up data: When making changes to large amounts of data, it’s always a good idea to back up your original data before making any modifications. This way, if anything goes wrong during the modification process, you still have access to the original data.

Here’s an example of how you can use the REPLACE function to modify a large amount of text data:

Suppose you have a list of customer names in column A of an Excel worksheet, and you want to replace all instances of “Ltd.” with “Limited” in each name. To apply this change to the entire column, you can use the following formula:

=IFERROR(REPLACE(A1,FIND(“Ltd.”,A1),LEN(“Ltd.”), “Limited”),A1)

This formula uses the REPLACE function to find and replace “Ltd.” with “Limited” in each cell in column A. The IFERROR function is used to return the original text string if the “Ltd.” string is not found in the cell.

To apply this formula to the entire column, you can copy and paste it down the entire column. If you’re working with a very large dataset and performance is an issue, you may want to divide the data into smaller chunks and apply the formula to each chunk separately.

In summary, the REPLACE function can be used to modify large amounts of text data in Excel. However, it’s important to consider factors such as performance, memory usage, testing, and data backup when working with large datasets.

Leave a Reply

Your email address will not be published. Required fields are marked *