Usage of REPLACE function in Excel

What is the REPLACE Function? Applying the REPLACE Function in Excel

The REPLACE function is a quick and easy way to replace a part of a string with another string based on a specified number of characters.

The REPLACE function is used in Excel:

• When you want to replace some characters in the old data string.
• It can be combined with other commonly used functions to support work tasks.
• In addition to the Replace function, the Find and Replace function available in Excel can also help you search and replace more easily.

If you don’t know how to use the REPLACE function in Excel, please follow the instructions below!

Note: The instructions are performed on a laptop running Windows 10 with Excel 2013. You can also perform the same steps on Excel 2007, 2010, 2013, 2016, 2109, and on laptops with the same operating system or MacOS line.

How to Use the REPLACE Function in Excel

REPLACE Function Syntax

REPLACE function syntax: =REPLACE(old_text, start_num, num_characters, new_text)

Where:

• Old_Text: This is the data string to be processed.
• Start_num: This is the position to start searching for what needs to be replaced, from the left.
• Num_chars: This is the number of characters in old_text that the REPLACE function will replace with new_text.
• New_Text: This is the string we want to replace the characters of old_text with.

Note: The REPLACE function always counts each character as 1, whether it is a single byte or a double byte, regardless of the default language.

How to Use the REPLACE Function

Example: Using the REPLACE function to replace the following characters.

Step 1: In the Excel calculation data table, you enter the formula =REPLACE(B5,FIND(C5,B5),LEN(C5),D5) in the reference cell where you want to display the result.

Where:

• REPLACE, FIND, LEN: These are function commands.
• B5: This is the data string to be processed.
• FIND(C5,B5): Determines the character to be changed to C5 in the B5 data.
• LEN(C5),D5: Measures the length of the character in cell C5 and changes it to the value in cell D5.

Step 2: Finally, you press Enter to display the result.

In addition to the Replace function, you can also see how to use the REPLACEB function. Here is a feature that most people are completely unaware of.

How to Use the REPLACEB Function in Excel

REPLACEB Function Syntax

REPLACEB function syntax: =REPLACEB(old_text, start_num, num_bytes, new_text)

Where:

• Old_Text: This is the data string to be processed.
• Start_num: This is the position to start searching for what needs to be replaced, from the left.
• Num_chars: This is the number of characters in old_text that the REPLACEB function will replace with new_text.
• New_Text: This is the string we want to replace the characters of old_text with.

Note: The REPLACEB function counts each double-byte character as 2 if you have turned on DBCS editing and set DBCS as the default language. Otherwise, the REPLACEB function will continue counting each character as 1.

How to Use REPLACEB

Example: Replace the first three characters of the number sequence 456789 with a single @ character.

Step 1: In the Excel calculation data table, you enter the formula =REPLACEB(B7,1,3,”@”) in the reference cell where you want to display the result.

Step 2: Finally, you press Enter to display the result.

Above is a detailed description of how to use the REPLACE function in Excel to replace a part of the text in Excel with easy-to-understand examples. We hope this article will help you perform the REPLACE function in work and study. If you have any comments, please leave them in the comments section below and don’t forget to share if you find it helpful. Thank you for following our article, see you in the next articles!

You can refer to some quality used machines, good prices from FPT shop.

• Cheap used machines

• How to easily and effectively fix excel formula not working
• How to number rows in Excel when deleting rows quickly and simply

You may also like

Easy and Effective Ways to Fix Excel Formula Not Working

Are you struggling with Excel formula errors? Do you find yourself unsure of the cause and how to fix it? Don’t worry, in today’s article, we will address all your concerns. Stay tuned to find out!