Table of Contents
To insert a character into a string using Google Sheets, you can use the CONCATENATE function. First, select the cell where you want the new string to appear. Then, type =CONCATENATE( in the formula bar. Next, select the existing string that you want to insert the character into, followed by a comma. Type the character you want to insert in quotation marks, followed by another comma. Lastly, select the remaining part of the string and close the parentheses. Press enter and the new string with the inserted character will appear in the selected cell.
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:
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: