Technology

An Easy and Clear Guide on How to Use REPLACE and REPLACEB Functions in Excel

The REPLACE and REPLACEB functions in Excel are closely related replacement functions that are widely used and versatile, helping office workers solve essential issues such as replacing a string of characters with a different text string in a simple and efficient manner.

Administrator

23 December, 2023

166

What is the REPLACE function, and how does it differ from the REPLACEB function? This article will help you understand the syntax and usage of the REPLACE and REPLACEB functions through some easy-to-understand examples. Let’s check it out together with FPT Shop!

What is the REPLACE function? Applications of the REPLACE function in Excel

What is the REPLACE function?

The REPLACE function is used to replace a part of a text string based on the number of characters you specify with another text string in a simple and fast way.

Function syntax: =REPLACE(old_text, start_num, num_chars, new_text).

Where:

Old_text: Required, it is the text string to be replaced.
Start_num: Required, it is the starting position counted from left to right.
Num_chars: Required, it is the number of characters in the text string to be replaced.
New_text: Required, it is the text string to replace the characters in old_text.

Applications of the REPLACE function in Excel

The REPLACE function in Excel is used to search for and replace text strings that need to be replaced.

In addition to using multiple nested REPLACE functions, it can also be combined with other common functions such as the TEXT, DATEVALUE, IFERROR, FIND functions, etc. to effectively solve related issues in work.

How to use the REPLACE function in Excel with examples

Nested REPLACE functions

Example 1: You want to change “ivi” in the text string “Tivi” to “V”.

Step 1: You need to determine the arguments:

Old_text: It is B2 which contains the text string to be replaced “Tivi”.
Start_num: It starts at position 2.
Num_chars: 3 characters to be replaced.
New_text: It is “V” to replace “ivi”.

Step 2: In cell C2, you enter the formula: =REPLACE(B2,2,3,”V”), and press ENTER to get the corresponding result “TV”.

Example 2: Suppose you want to change “123456789” to “123-456-789” by using multiple nested REPLACE functions:

Step 1: You identify the character strings to be changed as “123456789” to “123-456-789”, which means you need to add “-” at the 4th and 8th character positions.

Step 2: In cell C2, enter the formula: =REPLACE(REPLACE(A2,4,0,”-“),8,0,”-“) and press ENTER to get the result “123-456-789”.

Combining REPLACE function with FIND

In the above example, we used multiple nested REPLACE functions to replace multiple character strings in a cell at once. However, this method will not be suitable if you apply it to a case where one or more character strings to be replaced appear at different positions in each cell of the worksheet. Therefore, you will have to combine the REPLACE function with the FIND function to quickly find the position of the first character to be replaced without having to do it manually.

Example: Suppose you have a list of email addresses of customer partners. Among them, one partner company has changed its domain from “abc” to “org”. Therefore, you need to update the email addresses accordingly.

Original email

[email protected]

Step 1: You need to identify the character string to be replaced as “abc” and the starting position of the character to be replaced in the text string using the FIND function. According to the formula: =FIND(“abc”,A2), you receive the result in cell B2 which is “11”.

Step 2: In cell B2, you enter =REPLACE(A2,FIND(“abc”,A2),3,”org”) and press ENTER to get the corresponding result “[email protected]“.

Step 3: You perform the COPY – PASTE operation to replace other cells in the table.

Step 4: In case the worksheet contains cells without values to be replaced, the returned result will be “#VALUE!” which means an error result. Therefore, you need to provide a replacement condition using the IFERROR function, which allows your formula to return the original email address instead of an error result.

In cell B2, you enter: =IFERROR(REPLACE(A2,FIND(“abc”,A2),3,”org”),A2) and press ENTER to display the result.

Step 5: You perform the COPY – PASTE operation to fill in the remaining cells.

Combining REPLACE function with TEXT function

In the case where the reference data is in the form of a date “11-10-23” and you want to change the month 10 to month 11. If you only use the formula “=REPLACE(A2,4,2,”11″)”, you will get “45211” which is not the desired result. Why is that so? In fact, “11-10-23” is just a representation of the total number of days, which is “45210”, so the REPLACE function changes the last 2 digits to “11” and returns the text string “45211”.

To overcome this situation, you use the combination of the REPLACE function and the TEXT function to convert the dates to text strings.

In cell B2, you enter “=REPLACE(TEXT(A2,”dd-mm-yy”),4,2,”11″)” and press ENTER. The result is “20-11-23”.

What is the REPLACEB function? What are the differences with the REPLACE function?

What is the REPLACEB function?

The REPLACEB function is used to replace part of a text string based on the number of bytes you specify with another text string.

Function syntax: =REPLACEB(old_text, start_num, num_bytes, new_text).

Where:

Old_text: Required, it is the text string to be processed.
Start_num: Required, it is the starting position counted from left to right.
Num_bytes: Required, it is the number of bytes in the text string to be replaced.
New_text: Required, it is the text string to replace.

Illustrative examples of the REPLACEB function

Example 1: You want to replace the first 3 characters “456” of the number sequence 456789 in cell B7 with the single character “@”.

Step 1: In cell E7, enter the formula =REPLACEB(B7,1,3,”@”).

Step 2: Press ENTER to get the corresponding result “@789”.

Comparison of differences between the REPLACE and REPLACEB functions

The REPLACE and REPLACEB functions are closely related replacement functions, and the difference between each function lies in the unit of characters in the text string to be replaced that you specify. If the text string to be replaced of the REPLACE function is based on the specified number of characters, the REPLACEB function will be based on the specified number of bytes.

The REPLACE function is used in languages that use single-byte character sets (SBCS). The REPLACE function always counts each character as 1 byte, regardless of the default language setting.

On the other hand, the REPLACEB function is used in languages that use double-byte character sets (DBCS). If you set a language that supports DBCS as the default on your computer, the REPLACEB function will count each double-byte character as 2 bytes, and if not, it will count each character as 1 byte.

Conclusion

FPT Shop wishes you successful implementation of the REPLACE and REPLACEB functions to support your learning and work. If you have any further questions, feel free to comment below for the admin to answer!