Google Sheets REPLACE Function

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

Description


The Google Sheets REPLACE function replaces all or part of a text string with another text string.

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

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

Syntax


The syntax of the REPLACE function is as below:

=REPLACE (old_text, start_num, num_chars, new_text)

Where the REPLACE 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_chars – 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 REPLACE function are treated as text string in google sheets and if you are using the REPLACE function with numbers in calculations, the error message may be returned.
  • The REPLACE 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 REPLACE function.
  • The REPLACE 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_chars is the number of characters 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_chars are not a positive number and the REPLACE function returns a #VALUE error.

​There is another replace function called REPLACEB function. It is designed to work with double-type characters set. Such as: simplified Chinese.

Google Sheets REPLACE Function Examples


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

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

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

google sheets replace function

#2 you can use the google sheets REPLACE function to replace unwanted text string in a cell with another text string or empty string.

When you copied or imported data from the external application, it may include unwanted characters or text string along with the good data. At this time, you can use the REPLACE function to remove it.

For example, if you want to replace the first character of the text string “#158” with a dollar sign, you can use the below REPLACE formula:

=REPLACE(B1,1,1,"$")

google sheets replace function

You will get the result as: $158 in the above formula.

#3 Replace text with an empty string for new text using REPLACE function. For example, replace “google” word with another word “excel”:

=REPLACE(“google sheets”,1,6,”excel”) //it returns “excel sheets”

google sheets replace function

#4 Using REPLACE function to remove the first 6 characters from a text string in Cell B1, type:

=REPLACE(B1,1,6,"")//it returns “2021 sheets 2020”

google sheets replace function

Note: You can use the REPLACE 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 or SEARCH function to get the location of a given text string in a text sting.

 

 

 

 

Related Posts

Google Sheets NOW Function
google sheets now function1

This post will guide you how to use Google Sheets NOW function with syntax and examples. Description The Google Sheets NOW function returns the serial number of the current date and time. And it will be updated each time when ...

Google Sheets NETWORKDAYS.INTL Function
google sheets networkdays.intl function1

This post will guide you how to use Google Sheets NETWORKDAYS.INTL function with syntax and examples. Description The Google Sheets NETWORKDAYS.INTL function returns the number of net working days between tow given days excluding specified weekend days and holidays. The ...

Google Sheets NETWORKDAYS Function
google sheets networkdays function1

This post will guide you how to use Google Sheets NETWORKDAYS function with syntax and examples. Description The Google Sheets NETWORKDAYS function returns the number of net working days between tow given days. The NETWORKDAYS function can be used to ...

Google Sheets MONTH Function
google sheets month function1

This post will guide you how to use Google Sheets MONTH function with syntax and examples. Description The Google Sheets MONTH function returns the month of a date represented by a serial number. And the month is an integer number ...

Google Sheets MINUTE Function
google sheets minute function1

This post will guide you how to use Google Sheets MINUTE function with syntax and examples. Description The Google Sheets MINUTE returns the minutes of a time value. And the minutes is an integer number from 0 to 59. So ...

Google Sheets ISOWEEKNUM Function
google sheets isoweeknum function1

This post will guide you how to use Google Sheets ISOWEEKNUM function with syntax and examples. Description The Google Sheets ISOWEEKNUM returns the number of the ISO week of the year for a given date. ISO weeks begin on Monday ...

Google Sheets HOUR Function
google sheets hour function1

This post will guide you how to use Google Sheets HOUR function with syntax and examples. Description The Google Sheets HOUR returns the hour of a time value. Or returns an integer value that represent the hour component of a ...

Google Sheets EOMONTH Function
google sheets eomonth function1

This post will guide you how to use Google Sheets EOMONTH function with syntax and examples. Description The Google Sheets EOMONTH returns a date representing the last day of the month, n months in the future or past. The EOMONTH ...

Google Sheets EDATE Function
google sheets edate function1

This post will guide you how to use Google Sheets EDATE function with syntax and examples. Description The Google Sheets EDATE returns a date that is a specified number of months before or after a specified date. You can use ...

Google Sheets DAYS360 Function
google sheets days360 function1

This post will guide you how to use Google Sheets DAYS360 function with syntax and examples. Description The Google Sheets DAYS360 will calculate the number of days between two dates based on a 360-day year. The DAYS360 function can be ...

Sidebar