When you need to search for a string in a text you will use the FIND () function, but to search and replace the new string directly, you need to use the REPLACE () or REPLACEB () function. If you do not know the syntax and usage of the two search and replace functions REPLACE (), REPLACEB (), you should follow the article below.
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)
Inside:
- 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 want to replace with the new string.
- num_bytes: the number of bytes in the old_text string that you want to replace with the new string.
- new_text: is the new text string that you want 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!