Insert a value after first nth character

This tutorial shows how to insert a value after the first nth character through the use of an Excel formula, with the REPLACE function or VBA

EXCEL FORMULA 1. Insert a value after first nth character using the REPLACE function

EXCEL

Hard coded formula
Insert a value after first nth character
Cell reference formula
Insert a value after first nth character
=REPLACE(B5,3+1,0,"x")
=REPLACE(B5,C5+1,0,D5)
GENERIC FORMULA

=REPLACE(string,nth_char+1,0,value)

ARGUMENTS
string: The string in which you want to insert a value after the first nth character.
nth_char: A number which represents after which character in the string you want to insert a specific value.
value: A value that you want to insert after the first nth character in a string.

GENERIC FORMULA

=REPLACE(string,nth_char+1,0,value)

ARGUMENTS
string: The string in which you want to insert a value after the first nth character.
nth_char: A number which represents after which character in the string you want to insert a specific value.
value: A value that you want to insert after the first nth character in a string.

EXPLANATION

This formula uses the REPLACE function to insert a specific value after the first nth character in a string.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the value and the number that represents after which character to insert the value entered directly in the formula or referenced to cells.

In this example we are inserting a value of 'x' after the third (3rd) character from the left in a string captured in cell B5.

VBA CODE 1. Insert a value after first nth character using VBA

VBA

Hard coded against single cell
Sub Insert_value_after_first_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a specific value after the first nth character
ws.Range("C5").Formula = "=REPLACE(B5,3+1,0,""x"")"

End Sub

Cell reference against single cell
Sub Insert_value_after_first_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a specific value after the first nth character
ws.Range("E5").Formula = "=REPLACE(B5,C5+1,0,D5)"

End Sub

Hard coded against range of cells
Sub Insert_value_after_first_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a specific value after the first nth character

For i = 5 To 8

ws.Range("C" & i).Formula = "=REPLACE(B" & i & ",3+1,0,""x"")"
Next i

End Sub

Cell reference against range of cells
Sub Insert_value_after_first_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a specific value after the first nth character

For i = 5 To 8

ws.Range("E" & i).Formula = "=REPLACE(B" & i & ",C" & i & "+1,0,D" & i & ")"
Next i

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Repeat a value n times in a cell How to repeat a value n number of times in a cell through the use of an Excel formula or VBA