Excel: How to Insert a Character into a String


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:

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

Featured Posts

Leave a Reply

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