Google Sheets: How to Insert a Character into a String


Often you may want to insert a character into a specific position of a string in Google Sheets.

You can use the REPLACE function with the following syntax to do so:

=REPLACE(A2, 5, 0, "sometext")

This particular formula inserts “sometext” into the string in cell A2 starting at position 5 of the string.

The following example shows how to use this formula in practice.

Example: Insert Character into String in Google Sheets

Suppose we have the following dataset in Google Sheets that shows the conference and team name of various basketball teams in the NBA:

Suppose we would like to insert “Conference” immediately after “East” in each string.

Since “East” is 4 characters long, we will use the following formula to insert “Conference” into the string starting at the fifth position:

=REPLACE(A2, 5, 0, " Conference")

We will type this formula into cell B2 and then click and drag the formula down to each remaining cell in column B:

Google Sheets insert character into string

Notice that “Conference” has been inserted into each string starting at position 5.

Also note that we intentionally left a space in the beginning of ” Conference” so that there would be a space between “East” and “Conference” in each string.

How This Formula Works

The REPLACE() function in Google Sheets uses the following syntax:

REPLACE(text, position, length, new_text)

where:

  • text: The text to replace
  • position: Starting position in text to search
  • length: The number of characters to replace
  • new_text: The text to replace text with

In our example, we used the following formula:

=REPLACE(A2, 5, 0, " Conference")

Thus, our formula replaced 0 characters starting at position 5 in cell A2 and the text we used was “Conference” which had the effect of inserting this text starting at position 5 rather than replacing any text in the original string.

Note: You can find the complete documentation for the REPLACE function in Google Sheets here.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Search for Special Characters in Cell
Google Sheets: How to Extract Text Between Two Characters
Google Sheets: How to Remove Last Character from String

Leave a Reply

Your email address will not be published. Required fields are marked *