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:
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
