REPLACE, REPLACEB functions

The REPLACE and REPLACEB functions in Excel are used to replace part of a text string with another text string. These functions are particularly useful when you need to change a specific portion of a text, based on its position, and replace it with new characters or values.

1. REPLACE Function

The REPLACE function is used to replace part of a text string based on the number of characters you want to replace and their position in the text string.

Syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

Parameters:

• old_text (required): The original text string in which you want to replace characters.
• start_num (required): The position of the first character to replace. The first character in the string is considered position 1.
• num_chars (required): The number of characters to replace, starting from the position specified by start_num.
• new_text (required): The new text to replace the old characters with.

Key Points:

• Replaces a specified number of characters in the text string starting from a given position.
• Can be used to replace a portion of the text with a different value.

Example Usage:

1. Replacing Characters in a Text String

If cell A1 contains “Hello World” and you want to replace the word “World” with “Universe”, the formula:

=REPLACE(A1, 7, 5, "Universe")

This will return:

Hello Universe

Explanation:

• The word “World” starts at position 7 and consists of 5 characters, so “World” is replaced with “Universe”.

2. Replacing Part of a Text String

If cell A2 contains the text “12345” and you want to replace the last 3 characters with “000”, the formula:

=REPLACE(A2, 3, 3, "000")

This will return:

12000

Explanation:

• The characters starting from position 3 are replaced with “000”, changing “345” to “000”.

2. REPLACEB Function

The REPLACEB function is similar to REPLACE, but it works with byte-based characters, which is useful for dealing with double-byte character sets (DBCS) like Japanese, Chinese, or Korean. In DBCS, characters may take up more than one byte, and REPLACEB is used to replace a specific number of bytes rather than characters.

Syntax:

=REPLACEB(old_text, start_num, num_bytes, new_text)

Parameters:

• old_text (required): The original text string in which you want to replace characters (in byte format).
• start_num (required): The position of the first byte to replace. The first byte in the string is position 1.
• num_bytes (required): The number of bytes to replace.
• new_text (required): The new text to replace the old characters with.

Key Points:

• The REPLACEB function operates on bytes, making it useful for text in languages like Japanese, where characters take up more than one byte.
• This function counts bytes rather than characters, so it is important when dealing with double-byte characters (DBCS).

Example Usage:

1. Replacing Characters with Byte-based Text

If cell A3 contains the Japanese text “こんにちは” (which consists of 10 bytes), and you want to replace the first 6 bytes (the first three characters) with “さようなら” (which takes up 12 bytes), the formula:

=REPLACEB(A3, 1, 6, "さようなら")

This will return:

さようならにちは

Explanation:

• The first 6 bytes (representing the “こん” portion) are replaced with the new text “さようなら” which uses 12 bytes.

2. Replacing Byte-based Characters in Text

If cell A4 contains the text “ABCDEF”, and you want to replace the first 4 bytes with “XYZ”, the formula:

=REPLACEB(A4, 1, 4, "XYZ")

This will return:

XYZEF

Explanation:

• The first 4 bytes (“ABCD”) are replaced with “XYZ”, modifying the string accordingly.

Key Differences Between REPLACE and REPLACEB:

• Character vs Byte-based:
  • REPLACE works with characters and is suitable for standard Latin-based text.
  • REPLACEB works with bytes, making it ideal for double-byte characters in languages like Japanese, Chinese, or Korean.
• Byte Counting:
  • In REPLACE, the number of characters is counted, regardless of whether they are single-byte or double-byte.
  • In REPLACEB, the number of bytes is counted, which may be different if the text contains double-byte characters (such as kanji or other non-Latin scripts).

Notes:

• REPLACE and REPLACEB are case-sensitive, meaning they will distinguish between uppercase and lowercase letters.
• REPLACEB is typically used when working with non-Latin characters, where each character may occupy multiple bytes.
• The start_num and num_chars/num_bytes parameters should be chosen carefully, especially when dealing with multi-byte characters to ensure proper replacement without affecting the text format.

Related Functions:

• SUBSTITUTE: Replaces occurrences of a specific substring with another substring, and works with all occurrences of a substring in a text.
• TEXT: Can be used for formatting numbers, dates, and text, but does not modify text content like REPLACE does.
• MID: Extracts a substring from a given text, useful in combination with REPLACE to isolate text parts before replacement.

Both REPLACE and REPLACEB are powerful tools for replacing specific parts of text in Excel, but REPLACEB should be used when working with multi-byte or non-Latin characters to avoid issues with text encoding.