How to use REPLACE, REPLACEBs function in Excel?

In Excel, the `REPLACE` and `REPLACEB` functions are used to substitute part of a text string with a different text string. The primary difference between these two functions is that `REPLACEB` is intended for use with double-byte character set (DBCS) languages, such as Japanese, Chinese, and Korean, whereas `REPLACE` is used for single-byte languages.

`REPLACE` Function

Syntax:

REPLACE(old_text, start_num, num_chars, new_text)

old_text: The original text string that you want to alter.
start_num: The position within the `old_text` where you want to start replacing text.
num_chars: The number of characters in `old_text` that you want to replace.
new_text: The text that you want to insert in place of the text removed.

Example:

Suppose cell A1 contains the text “HelloWorld”, and you want to replace “World” with “Excel”. You would use the formula:

=REPLACE(A1, 6, 5, "Excel")

This would result in “HelloExcel”.

`REPLACEB` Function

Syntax:

REPLACEB(old_text, start_num, num_bytes, new_text)

The syntax is similar to `REPLACE`, but `REPLACEB` works based on bytes, not characters. This function is generally used in languages where the characters are often more than one byte. If you’re using `REPLACEB` in a language that uses single-byte character sets, it behaves the same as `REPLACE`.

old_text: The original text string that you want to modify.
start_num: The position in the `old_text` where you want to start replacing text.
num_bytes: The number of bytes in `old_text` that you want to replace.
new_text: The text string that you want to use to replace the specified bytes.

Note:

Excel needs to recognize the active language setting as a DBCS language for `REPLACEB` to function as intended.
If you don’t use DBCS languages or your setup doesn’t recognize DBCS, `REPLACEB` effectively works like `REPLACE`.

When to Use

Use `REPLACE` when replacing parts of strings for single-byte character sets.
Use `REPLACEB` when dealing with DBCS languages in environments where those settings are active.

For most basic uses, especially in Western languages, `REPLACE` will be the function you typically use. Remember to ensure that the starting position and number of characters to replace are specified correctly to achieve the desired text modification.

facebooklinkedin