Insert a value before first nth character

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

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

EXCEL

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

=REPLACE(string,nth_char,0,value)

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

GENERIC FORMULA

=REPLACE(string,nth_char,0,value)

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

EXPLANATION

This formula uses the REPLACE function to insert a specific value before 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 before 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' as the third (3rd) character from the left in a string captured in cell B5.

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

VBA

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

End Sub

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

End Sub

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

'insert a specific value before the first nth character

For i = 5 To 8

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

End Sub

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

'insert a specific value before the first nth character

For i = 5 To 8

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

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert a value after nth character How to insert a value after the nth character through the use of an Excel formula or VBA
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