Often you may want to insert a character into a specific position of a string in Excel.
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 Excel
Suppose we have the following dataset in Excel 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 Excel uses the following syntax:
REPLACE(old_text, start_num, num_chars, new_text)
where:
- old_text: The text to replace
- start_num: Starting location in text to search
- num_chars: The number of characters to replace
- new_text: The text to replace old_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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Remove Specific Text from Cells
Excel: A Formula for MID From Right
Excel: How to Use MID Function for Variable Length Strings