ExcelHow

Google Sheets REPLACEB Function

This post will guide you how to use Google Sheets REPLACEB function with syntax and examples.

Description

The Google Sheets REPLACEB function replaces all or part of a text string with another text string based on a number of bytes.

The REPLACEB function can be used to replace characters specified by position in the given text string with another newly text string in google sheets.

The REPLACEB function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REPLACEB function is as below:

=REPLACEB (old_text, start_num, num_bytes, new_text)

Where the REPLACEB function arguments are:

  • Old_text -This is a required argument. The text string that you want to replace all or part of
  • Start_num – This is a required argument. The position of the first character that you want to replace within old_text.
  • Num_bytes – This is a required argument. The number of characters that you want to replace within old_text
  • New_text – This is a required argument. The new text that will replace characters in old_text text string.

Note:

  • The returned results of REPLACEB function are treated as text string in google sheets and if you are using the REPLACEB function with numbers in calculations, the error message may be returned.
  • The REPLACEB function returns text as the output. And if you wish to return a number, and you can try to use the VALUE function in combination with the REPLACEB function.
  • The REPLACEB function takes 4 required arguments. The first argument, old_text, is the text string that a part of which will be replaced. The second argument, Start_num is the position where the replacement will begin to replace. The third argument, Num_bytes is the number of bytes in the text to be replaced. The last argument, New_text is the text which will be inserted into the original text string.
  • If both Start_num and Num_bytes are not a positive number and the REPLACEB function returns a #VALUE error.
  • It is designed to work with double-type characters set. Such as: simplified Chinese.
  • REPLACEB function will return the same value as REPLACE function if the old_text has only single byte characters.

Google Sheets REPLACE Function Examples

The below examples will show you how to use google sheets REPLACEB function to replace part of a text string with another new text string.

#1 To replace 4 bytes in B1 cell with a new text string and starting with 7th character in old_text text, just using formula:

=REPLACEB(B1,7,4," replace") //it returns “google replace1 sheets 2020”

google sheets replaceb function1

Note: You can use the REPLACEB function to replace a newly text string at a given position in a text string. And you can use the SUBSTITUTE function to replace an existing text with a new text in a string. And Using FIND function and SEARCH function to get the location of a given text string in a text sting.

 

Recent Posts