REPLACE and REPLACEB (Replace Part of the Input Text String)

This article provides a clear overview of REPLACE and REPLACEB (Replace Part of the Input Text String), with the main facts, useful context, and practical details organized for easy reading.

The article presents syntax and usage of REPLACE (), REPLACEB () in Excel.

Description

The REPLACE () and REPLACEB () functions are both functions that replace a part of the input text string.

The REPLACE () function helps you replace part of the input text string with another text string, based on the number of characters you specify. This function is used in languages that use single-byte encoding (SBCS). The REPLACE () function always counts each character as 1 whether it's a single byte or double byte regardless of the default language.

The REPLACEB () function helps you replace one part of the input text string with another, based on the number of bytes specified. Functions for languages that use double-byte encoding (DBCS). The REPLACEB () function counts each double-byte character as 2 when you use the default language, DBCS, otherwise the function will count each character as 1.

Syntax

= REPLACE (old_text, start_num, num_chars, new_text)

= REPLACEB (old_text, start_num, num_bytes, new_text)

Arguments

- old_text: the text string you need to replace some characters.

- start_num: position of the character in the text string to be replaced, starting from the left.

- num_chars: the number of characters in old_text that you may need to replace with the new string.

- num_bytes: the number of bytes in the old_text string that you may need to replace with the new string.

- new_text: is the new text string that you may need to replace the characters in the old_text string.

For Example

- Replace 6 characters starting from the 16th character in the string in cell B5 with the new string that is. vn

- In addition, instead of counting the starting characters or the number of characters to replace, you can use the FIND () or SEARCH () function to determine the start position (start_num) and use the LEN function. () to determine the number of characters of the string to be replaced (num_chars).

= REPLACE (B5, FIND ("dkfuef", B5), LEN ("dkfuef"), ". Vn")

So you know how to use the REPLACE () and REPLACEB () functions in Excel, you should combine with other functions to achieve the best effect. Good luck!